5 Alternative Reasons to Use COUNTIF Function

The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing reports and dashboards.

In this blog post will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF examples we look at are;

  1. Prevent duplicates in a range.
  2. Uniquely rank items in a list.
  3. Count the unique entries in a list.
  4. Compare two lists.
  5. Identify names that occur 3 times or more.

Continue reading

Sum Negative Values Only in a List

You may have scenario on a spreadsheet where you only want to sum the negative values in a range.

Take the example below for instance, it contains a list of incoming and outgoing payments. If we needed the total for outgoings, we would need to sum the negative values only.

Sum negative values only

This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition (new to SUMIF? find out more).

The formula below will total the outgoings in the list by summing only the values less than 0.


The criteria has been entered as text in double quotes. When entering criteria like this into the formula it needs to be entered as text.

The criteria specifies that only values less than 0 should be summed.

Watch the video

Advanced Techniques for Summing Values

Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.

This article looks at 4 advanced techniques for summing values including;

  1. Summing a range that contains errors
  2. Creating a cumulative total
  3. A consolidated sum from multiple sheets
  4. Summing Every Nth Value in a Range

Continue reading

Create a Picture Lookup in Excel

Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup to return a picture dependent upon the contents of a cell.

This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.

In this blog post we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.

Continue reading

Separate Text into Different Cells

A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.

This article looks at four techniques for separating text. Use the links below to jump to a specific technique.

Continue reading

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