I realised the other day that I had never covered one of my most used functions on this blog - SUMIFS. I have covered its predecessor, SUMIF, as SUMIFS has only been available since Excel 2007.
Although SUMIF is still available in later versions of Excel for compatibility purposes, it is essentially redundant, as SUMIFS does the same thing, plus a lot more.
Say we want to know how much Mary's sales were, or how much Sarah sold in the East Region, or even how much Ben sold in the North region in the month of January.
SUMIFS can do all of these.
The syntax for SUMIFS is as follows:
=SUMIFS(SumRange,CriteriaRange1,Criteria1,[CriteriaRange2],[Criteria2].....)
You can have as many pairs of CriteriaRange and Criteria as you need. The function works as follows:
SUM SumRange where CriteriaRange1 = Criteria1 and CriteriaRange2 = Criteria2 etc. for however many criteria you have.
For all of the examples above the SumRange will be D2:D21, as this is the range we want to sum, subject to our criteria. We will look at how we construct the rest of the formula for each of our examples above.
How much did Mary sell?
Here we only have one criteria:
CriteriaRange1 = C2:C21
Criteria1 = "Mary"
=SUMIFS(D2:D21,C2:C21,"Mary")
returns £16,853.
How much did Sarah sell in the East Region?
This time we have two criteria:
CriteriaRange1 = C2:C21
Criteria1 = "Sarah"
CriteriaRange2 = B2:B21
Criteria2 = "East"
=SUMIFS(D2:D21,C2:C21,"Sarah",B2:B21,"East")
returns £1,085.
How much did Ben sell in the North Region in the month of January?
This time we actually have four criteria:
CriteriaRange1 = C2:C21
Criteria1 = "Ben"
CriteriaRange2 = B2:B21
Criteria2 = "North"
CriteriaRange3 = A2:A21
Criteria3 = ">="&DATE(2016,1,1)
CriteriaRange4 = A2:A21
Criteria4 = "<="&DATE(2016,1,31)
There are two elements to these last two criteria that need further explanation.
The first is that if our criteria is anything other than equals, we need to include the criteria in inverted commas, for example ">23", or "<=15", to make it a string. If rather than 23, we wished to refer to a cell (say G5) we can use the ampersand (&) to join two strings together, e.g. ">"&G5.
The second is that if we wish to refer to a date directly, we need to refer its sequential number which we can calculate using the DATE function. The three arguments for the DATE function are Year, Month and Day, so to get the date sequence number for 1st January 2016, we can use DATE(2016,1,1). Note that if we entered 1/1/2016 in cell G5, we could just use ">="&G5 for Criteria3, as the cell value when you enter a date, is its date sequence value.
Our function is therefore:
=SUMIFS(D2:D21,C2:C21,"Ben",B2:B21,"North",A2:A21,">="&DATE(2016,1,1),A2:A21,"<="&DATE(2016,1,31))
which returns £4,007.
In most real situations we are likely to have all of the criteria in other cells, as we are usually doing more than one calculation.
With careful planning and smart use of dollar signs, you can structure your formula so that you only need to write it once. For example if we wish to populate the following grid from our data:
If we put the following function in cell J2, we can copy it to all of the other cells:
=SUMIFS($D$2:$D$21,$C$2:$C$21,$I2,$B$2:$B$21,J$1)
We have used dollars to fix both the rows and columns of the references to the data table, as these should not change, no matter what cell that we are in.
For our criteria however, we want those to change between cells, so we have fixed the column of our name criteria ($I2), as we are always going to look to column I for the name, but want it to change as we change rows. Similarly, we have fixed the row of our region criteria (J$1), as we always want to look at row 1, but want it to change with the columns.
Hopefully this gives you an idea how flexible SUMIFS can be.
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
If, as a convoluted example, you want all the sales for Salespersons beginning with M, putting a LEFT function in will not work (or any function in the criteria range)
ReplyDeleteFor that, you need to add a helper column or use SUMPRODUCT to produce a nightmare like this:
=SUMPRODUCT($D$2:$D$21*(LEFT($C$2:$C$21)="M"))
A much better solution for nearly all these situations is to use a Pivot Table (even better if you format your data as a Table first - which may even do the job by itself)
jim
Or use wildcards in the criteria, so in Jim's example you could write
ReplyDelete=SUMIFS(D2:D21,C2:C21,"M*")
Thanks Paul. I probably should have covered wildcards!
Deleteyes, that works
ReplyDeletestill learning after all these years
last para still applies (even in a simple example)
jim
Me too Jim! I nearly mentioned both PivotTables and helper columns but thought the post was already quite long and wanted to stick to the priority of explaining SUMIFS.
DeleteI tend to go to PivotTables as a first port of call for this kind of thing, but they are not appropriate in every situation - and this post was about SUMIFS.
In practice, I would typically use a helper column for the month, i.e. =MONTH(A2), but I thought it would be better to explain how to use < and > with SUMIFS.
I really like Sumifs and countifs. It helped a lot.
ReplyDeleteThank u :)
ReplyDelete