Sum/Count occurrences of a value – SUMIF/COUNTIF

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

In the Topic Example Workbook, say we want to produce a summary of our data by reporting the number of sales and total quantity sold by Salesperson. We could use a Pivot Table to do this, though instead if we have a separate list of Sales People (e.g. by filtering for unique values) then we can tie up the two lists by using the SUMIF and COUNTIF formulas which will allow us to take each salesperson in turn and work out the number of sales/sum of total quantity sold for each one.

This is achieved using the COUNTIF function (which allows you to count the number of occurrences of something within a range) and the SUMIF function (which allows you to Sum values in one column where the corresponding value in another column meets a condition you specify).

As well as counting/summing occurrences of a value, these functions are useful for doing some quick analysis. For example the COUNTIF formula can be used for counting the number of sales which are greater than a threshold which you specify, or to check whether there are duplicated values within a range.

The syntax of the COUNTIF formula is =COUNTIF(range, criteria). The range is the selection of cells we want to look across for our value (e.g. the list of salespeople in the range $A$2:$A$9) and the criteria is the value you want to look for (e.g. “Andrew”, the value in cell G3). The COUNTIF formula then returns the number of cells in the range that are equal to the value you have specified. In this case, this will return the number of occurrences of the salesperson in the range – i.e. the number of sales attributable to that salesperson.

In our example,say we want to count the number of sales for each salesperson in the range G3:G7 in turn. Starting with the formula required in cell H3, the range we want to look across is the list of salespeople in the range $A$2:$A$9. The value we want to look for is the salesperson in cell G3. So our COUNTIF formula becomes =COUNTIF($A$2:$A$9,G3). This returns a value of 3, indicating that the value “Andrew” occurs 3 times in the range $A$2:$A$9 – i.e. there are 3 sales attributable to Andrew.

Note that when entering the range into our COUNTIF formula, ensure that it is entered as an absolute reference so that it doesn’t change as the formula is dragged down. When entering the formula, once you have selected the range, press F4 to automatically insert the $ sign around the references.

If instead of counting occurrences of an exact value wanted to find the number of records containing a value then use Wildcards around the criteria e.g. to count all the values in the range A2:A9 which contains the text in cell G3 the SUMIF formula would become =COUNTIF($A$2:$A$9,”*”&G3&”*”)

You can combine the COUNTIF/SUMIF formulas with condition operators such as “>”, (greater than), “>=” (greater than or equal to”, “<” (less than), “<=” (less than or equal to) and “<>” (not equal to). Note that these condition operators must be contained within double quotes if they are to be combined with a cell reference.This is done using the & character. For example the condition ‘Sales greater than 300’ can be written as “>300” or if the number 300 is written in cell B20 then the condition is written as “>”&B20. So our completed COUNTIF formula for counting the number of sales greater than the value in cell B20 is =COUNTIF($D$2:$D$9,”>”&B20).

The COUNTIF formula can also be used to identify duplicate values within a range by identifying where the number of occurrences within a range are greater than 1. To do this we simply combine the COUNTIF function with an IF statement. To see whether the value in cell A2 is duplicated within the range $A$2:$A$9 (returning “Y” if it is and “N” if it isn’t), we could write this as =IF(COUNTIF($A$2:$A$9,A2)>1,”Y”,”N”), Again it is important we use dollar signs (indicating an absolute reference) around our range to ensure it stays the same even when we drag the formula down.

Being even smarter with our use of dollar signs we can use COUNTIF not just to indicate if a value occurs more than once but to actually return the occurrence number of a value within a range. THis might allow you to apply some logic only for the first occurrence with separate logic for other occurrences. To do this we need to lock the first cell of the range in the formula (so B2 becomes $B$2) but leave end cell of the range as relative so that it changes as the formula extends down. So in cell C2 our formula is =COUNTIF($B$2:B2,B2) which returns 1 but as the formula drags down one row the formula becomes =COUNTIF($B$2:B3,B3) which now shows 2 as it is the second occurrence of ‘Digital Sales’ within the range $B$2:$B$9.

If instead of counting the number of sales attributable to a salesperson we wanted the total sales attributable to them, we could use a SUMIF formula. To obtain the total sales attributable to Andrew for example, we would want to sum the values in the range $D$2:$D$9 where the corresponding value in the range $A$2:$A$9 is “Andrew”.

The syntax of the SUMIF formula is =SUMIF(range, Criteria, [Sum Range]). The range and criteria elements are the same as in a COUNTIF formula – the range is the list of salespeople ($A$2:$A$9), and the criteria is the value in cell G3 (Andrew). The sum range is the corresponding values we want to sum – in this case the quantity sold ($D$2:$D$9). So our completed SUMIF formula is =SUMIF($A$2:$A$9,G3,$D$2:$D$9). This returns a value of 1500 which is the sum of values in cells D2, D8 and D9 (Sales by Andrew).

The same considerations of ensuring the references are prefixed with dollar signs and the possibility of using wildcards applies to the SUMIF formula as well. The only other thing to be aware of is that the range to sum and the range to look across are the same height – in this case both spanning rows 2 to 8. The formula won’t give an error if the ranges are differently sized but you may end up with the wrong result! It is also possible to use condition operators with SUMIF in the same way, for example to find all the sales not attributable to Andrew.

In the Topic example workbook:

• In cells H3:H7 use a COUNTIF formula to count the number of sales attributable to each salesperson in the range G3:G7
• In cells I3:I7 use a SUMIF formula to find the total quantity sold by each salesperson in the range G3:G7
• In cells H12:H15 use a COUNTIF formula to count the number of sales attributable to each department in the range G12:G15
• In cells I12:I5 use a SUMIF formula to find the total quantity sold by each department in the range G12:G15
• In Cells C20:C22 write a SUMIF/COUNTIF formula that represents the condition/value in the range A20:B22. For example in cell C20 write a formula that returns the number of sales which are greater than 300.
• In Cells E2:E9 write a COUNTIF formula to determine whether the corresponding Salesperson in column A is duplicated in the range $A$2:$A$9. Return “Y” if it is duplicated, “N” if it isn’t.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

• When using a SUMIF/COUNTIF formula to analyse for each unique value in a range, always do a reconciliation of the total to ensure that you are accounting for each value. For example we should check that the sum of values in cells I3:I7 is equal to the total of our sales in cells D2:D9. If not, this suggests that there is a salesperson missing in our range G2:G7.

YouTube Video – SUMIF and COUNTIF functions in Excel