Sum/Count rows based on multiple conditions or values within a range – using SUMIFS/COUNTIFS

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.

The SUMIF/COUNTIF functions are very useful but one limitation of them is that they only allow us to take into account one criteria/field at time – we could use it to analyse the data by salesperson or department but not a combination of both. In versions of Excel prior to 2007, we could only get around this by either using a Concatenation Column or SUMPRODUCT formula. From Excel 2007 onwards, functions SUMIFS and COUNTIFS were introduced which allow us to take into account multiple criteria – e.g.. both Salesperson and Department. This allows us to analyse our data at a more granular level. Whilst you can use only one condition within a SUMIFS/COUNTIFS formula, and hence you don’t need to use SUMIF/COUNTIF in Excel 2007 onwards, it is still useful to review the SUMIF/COUNTIF topic before this one as the syntax is similar and will make this topic easier to understand.

Apart from summing/occurrences of values across multiple columns, another use of the SUMIFS/COUNTIFS formulas is to count or sum occurrences within a range – for example counting the number of sales which are between 300 and 700. This is done by combining conditional operators (e.g. “>”, “<”) with our SUMIFS/COUNTIFS formulas. Note that these formulas are from Excel 2007 onwards only -if you attempt to use a new Excel 2007 function and open it in Excel 2003 it will show as a ‘#Name’ error.

The syntax of the COUNTIFS formula is =COUNTIFS(Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2….). The first criteria range is the first set of values we want to look across (e.g. salespeople in the range $A$2:$A$9); the first criteria is value we want to look for in that range (e.g. “Andrew”, the value in cell A20). The second criteria Range is the second set of values we want to look for (e.g. the list of Departments in the range $B$2:$B$9) and the second criteria is the value we want to look for in that range (e.g. “Digital Sales”, the value in cell B20). So the COUNTIFS formula we need in Cell C20 is =COUNTIFS($A$2:$A$9,A20,$B$2:$B$9,B20). This will return a value of 1 indicating that although Andrew sold 3 products in total (as seen from our COUNTIF formula), the number sold by Andrew in the Digital Sales Department is only 1.

The SUMIFS formula works in exactly the same way though starts with the range of cells to sum (the list of quantity sold cells – the range $D$2:$D$9). The syntax of the SUMIFS formula is =SUMIFS(Sum Range, Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2….). Applying this to our situation, the completed formula in cell D20 is =SUMIFS($D$2:$D$9,$A$2:$A$9,A20,$B$2:$B$9,B20). This returns a value of 100 which is the sum of Sales by Andrew in Digital sales – i.e. the value in cell D2 (as Andrew and Digital Sales is a unique combination). Once we drag this formula down we can see that in cell D26 we get a value of 1400 – the sum of values in cells D8 and D9 which are attributable to both salesperson Andrew and department Hardware.

You can combine the COUNTIFS/SUMIFS 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 B14 then the condition is written as “>”&B14.

This can be used to count/sum occurrences of values occurring within a range – for example if we had a list of dates, we could count/sum the number of values for each month by applying 2 conditions: the first is that the value has to be greater than or equal to each start date (e.g. ‘01/01/2012’) and the second is that it has to be less than each end date (e.g. 01/02/2012) – combining these into a COUNTIFS/SUMIFS formula would give us the number/sum of values within each range (e.g. all those occurring in Jan).

Applying this to our Topic Example workbook, we might want to know how many sales are greater than or equal to 300 (the value in cell B14) but less than or equal to 700 (the value in cell C14), for this we could use a COUNTIFS formula that would read =COUNTIFS($D$2:$D$9, “>=”&B14, $D$2:$D$9,”<="&C14).

In the Topic Example Workbook, complete the required formulas in the yellow cells:

• In cells H3:H9 use a COUNTIFS formula to count the number of sales for the salesperson in cells F3:F9 and Department in G3:G9.
• In cells I3:I9 use a SUMIFS formula to count the number of sales for the salesperson in cells F3:F9 and Department in G3:G9.
• In Cells D14:D15 use a SUMIFS/COUNTIFS formula combined with a condition operator for the condition specified in cells A14/A15.

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.

• The criteria within a SUMIFS/COUNTIFS formula are combined with an ‘AND’ condition – i.e. you sum or count the values where Criteria 1 AND Criteria 2, Criteria 3 etc are met. If instead you wanted to do an or condition, then you can have a helper column which uses the [OR function] to return a true/false value and then base the SUMIFS/COUNTIFS formula on this. Alternatively, you can use the [SUMPRODUCT] formula.

YouTube Video – Excel SUMIFS Sum With Multiple Criteria