Count the Unique Entries in a range

A common question from Excel analysts and enthusiasts on my courses is to count the number of unique entries in a list.

This post looks at using a formula to calculate this distinct count.

Consider the list below of a list of delegates attending our courses. A normal count on this range will tell us how many attendances there were. That’s good, but we want to know how many unique attendees there were.

List of delegates

Continue reading

Sum Every Nth Row in a List

At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.

The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.

Continue reading

Sum a Column Ignoring Formula Errors

Having formula errors on a spreadsheet can be bad news. They look ugly, they frighten the less Excel experienced among us and they stop our other formulas from working.

If you have formula errors on a spreadsheet it is normally best to stop it at its source. To either correct the error, or to hide it using formulas such as ISERROR and IFERROR.

However, if your spreadsheet is large, having these IFERROR functions in every cell to protect against error values will add more calculation time to your spreadsheet.

There is a function in Excel called AGGREGATE which allows us to perform various functions on a range whilst ignoring formula errors.

Continue reading

Using the Hyperlink Function in Excel

Hyperlinks can be used to link to other sheets, webpages or other files such as PDF’s from your Excel spreadsheet. If you are creating hyperlinks for many records though this will take a long time to set them up.

Excel provides a HYPERLINK function for creating hyperlinks in our spreadsheets. The real power behind this function is that it can be used to create dynamic hyperlinks.

We can create conditional hyperlinks by nesting them within an IF function, or create hyperlinks that can find the address to link to themselves by embedding Lookup or Text functions within them. This helps us to create automated and error resistant spreadsheets.

Continue reading

Learning Excel – 5 Awesome Date Functions in Excel

Do you want to advance your skills by learning Excel? Performing date calculations can sometimes be troublesome. Trying to calculate the number of working days between two dates, or automatically finding the date in two months’ time is not as straight forward as formulas with general numbering can be.

Fortunately Excel has a full repertoire of fantastic date functions. Here are five of the best.

Continue reading

Using SUMPRODUCT Function to Count Records using Multiple Conditions

There are many ways to count records using multiple conditions in Excel. These can range from using the COUNTIFS function, PivotTables or by simply filtering the data. However neither of them are as flexible as the SUMPRODUCT function.

The SUMPRODUCT function is used to multiply the values of multiple arrays and then sum the totals. However it is capable of so much more amazing feats. It truly is one of those hidden gems waiting to be discovered.

Continue reading

Learn How to Use Excel – 3 Special Excel Functions that will Amaze You

 

Excel contains 400+ functions and this list is constantly growing. There are functions to perform almost any task from financial, date and time, statistical etc.

This learn how to use Excel post looks at 3 little known special Excel functions that will take your skills to another level and make you the envy of your work colleagues.

Continue reading

Excel Training – Lookup your Data with the DGET Function

A key skill in Excel training is to be able to lookup and retrieve data from a range of records. The most popular way of achieving this is to use the Vlookup function. The Vlookup function on Excel is awesome and easy to use, but it has its drawbacks.

Cue the DGET function. A very powerful Excel function that will retrieve data from a record without the limitations of Vlookup. Advantages of using the DGET function include:

  • It can retrieve data from a column to the left of the column you searched within.
  • It can lookup data based on multiple conditions.
  • It can handle both AND and OR logic.

Continue reading

Count the Number of Words in a Cell

Working recently at a large Internet company they needed to find how many words were in a cell. This was because they had imported hundreds of thousands of keywords that customers had used to find their site through search engines.

To analyse this data they wanted to count how many words were in each cell containing keyword searches. provides many text functions for managing and manipulating the text in the cells of your spreadsheet. The following formula did the job.

Continue reading