SUMIF is a commonly used function to give you sum of a range that meets a specific criterion. For the example above, if you want to know the total expense of Department 1 in Jan, you can use SUMIF:
The Syntax
SUMIF(range,criteria,sum_range)
This is the formula used in the above example:
=SUMIF($B$3:$B$16,"Department 1",$C$3:$C$16)
- where B3:B16 is the range with the criteria;
- “Department 1” is what you look for;
- C3:C16 is the range of the data you need to add
Altogether, it means please give me the total expense of Department 1 in Jan.
Instead of “hard-coding” the Department, we can substitute “Department 1” with the cell reference $G$3 where the department will be input to:
=SUMIF($B$3:$B$16,$G$3,$C$3:$C$16)
With this modification, we can get the result for different departments by changing the value in G3. That is easy.
Could we also add the second variable for Month, so that we can get the answer for different month according to the value input in H3?
The answer is YES! Thanks to the interesting characteristic of the sum_range argument.
The sum_range argument does not have to be the same size and shape as range. The actual cells that are added are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range.
For example:
| If range is | And sum_range is | Then the actual cells to be summed are |
| A1:A5 | B1:B5 | B1:B5 |
| A1:A5 | B1:B3 | B1:B5 |
| A1:B4 | C1 | C1:D4 |
| A1:B4 | C1:C2 | C1:D4 |
Now look back at our example. The range is B3:B16, we may input C3 as the sum_range for the month of Jan; D3 for Feb; and E3 for Mar.
=SUMIF($B$3:$B$16,$G$3,$C$3)
The actual cells to be summed for the above formula are C3:C16 (which is the same size of the range).
Now with this “simplified” sum_range, we may make it dynamic by using INDEX and MATCH:
=INDEX($C$3:$E$3,MATCH($H$3,$C$2:$E$2,0))
where MATCH is used to identify the position of the Month input in H3.
When H3 is Jan, Feb or Mar, it returns 1, 2 or 3 respectively.
Wrapped with INDEX,
- INDEX(C3:E3,1) returns a cell reference of C3
- INDEX(C3:E3,2) returns a cell reference of D3
- INDEX(C3:E3,3) returns a cell reference of E3
Thanks to the reference form syntax of INDEX.
By putting all together, this is the final formula to make the SUMIF dynamic with 2(D) variables (G3 for department; H3 for month):
=SUMIF($B$3:$B$16,$G$3,INDEX($C$3:$E$3,MATCH($H$3,$C$2:$E$2,0)))
What do you think? Please leave your comment. 🙂
wmfexcel 
Hi!!! Please advise if I want to sum from Jan to March, Department 1 only How to use formula
Thank you for advise.
LikeLike
=SUMIF(NICS!$C:$C,$B17,INDEX(NICS!$D$1:$W$1,MATCH($D$1,NICS!$D$7:$W$7,0)))+SUMIF(NICS!$C:$C,$B17,INDEX(NICS!$D$1:$W$1,MATCH(‘Physician List’!E18,NICS!$D$7:$W$7,0)))
Hi, it’s probably something very simple…I’ve basically figured out this formula knowing what I needed done and then a lot of googling…:) and now I’m stuck…:(…
I have a spreadsheet that has the above formula throughout. I don’t want to delete it and re-enter in August because it is in various areas in the entire spreadsheet. I don’t want to have to figure out where to plug them in come August. Please help.
The second half of the formula is pretty much a repeat of the 1st half. The second half is only needed in August. In August an extension of the main August report is run to capture final expenses. We are now in September and it is is now giving me an error (#N/A). I’ve tried entering Iferror(,””) to the whole formula which returns a blank cell and I’ve entered it for the last formula only and it will not allow it. I am needing the 1st half of the formula to work to capture the current months results and needing it not to recognize the 2nd half of the formula until there is something listed in “Physician List E18” which will not be until our 2nd close in August. Please let me know how to fix this…if possible. Thank you!
LikeLike
Very useful, thank you. Excuse me if this is silly question, but can this formula be adapted to capture a range of months? So for example, I want to look up the cumulative expenses for Department 2 to Feb. As before I want to look up the department in the vertical list and the month in the horizontal list, but I want to say sum that month and x many months before. I think it might be an OFFSET, but I’m utterly confused. Amazing if you can help.
LikeLike
Hi Laura,
Yes. Offset can do what you want… but you will also need SUMPRODUCT.
Based on the layout in the example, you may try the following formula:
=SUMPRODUCT(–($B$3:$B$16=$G$3)*OFFSET($C$3,0,0,14,MATCH($H$3,$C$2:$E$2,0)))
LikeLike
That’s excellent thank you. Sumproduct has always been a mystery to me. I’ve applied the formula to my data, and the only issue is that I want to be able to specify how many months are picked up in the solution. So if I’ve got 100s of months across the top of my data set, and departments down the side, in my formula I want to pick up the last 12 months of data for a selected department ending in a selected month. So my formula needs to have the potential to pick up all the months, but then depending on the month I select, I want to sum just the 12 months to that month. Is that possible? Thank you so much for your help, this is a godsend.
LikeLike
Hi Laura,
Please check today’s post for a potential solution to your question…
https://wmfexcel.com/2017/05/13/crosstab-sum-with-3-variables/
Hope that I have understood your question correctly. 🙂
LikeLike
Thank you for the detailed walkthrough! You just save me a few hours.
LikeLike
You are welcome. Glad it helps
LikeLike
Hi Thank you for your excel support I would like to ask you if I have to sum by 3 criteria For example I have 1st column month second column project number 3td column accounts number and 4th column sum In another worksheet I have to fill in the sum for defined accounts (which I should take from 3th column) and match by month and project number Thanks for your help
LikeLike
Perform VLOOKUP with 2 lookup values | wmfexcel
https://wmfexcel.com/2014/05/11/perform-vlookup-with-2-lookup-values-2/
If you get this, you may try to do it with additional value (column)
Alternative Pivot Table maybe an easier way.
LikeLike
Hi – Thank you for your really fast answer. I know for the pivot table, but it is not working for me unfortunately. VBA will, but I would like to do it for now with excel functions. In fact I have 2 files – one is with the data I already explained – columns: month, project number, accounts, 4-th column sum. In the second file I have more columns: 1st column month, 2nd column project number, 3th column external cost (there I should have the sum of some of the accounts in the 3thd column from the 1st file), 4th row internal costs (sum of some of the accounts from the 3th column from the 1st file) etc… (something like transposing sum of rows) One decision is to use helper column in the first file – where to sum of the accounts I need – but I try to learn now to use complex formula and think that with sumifs and index match I could make it – but somewhere I’m doing wrong and didn’t get it I really appreciate if you help for syntax the formula
LikeLike
Hi Gergana,
It’s a bit difficult for me to “visualize” your data and understand your request… Perhaps you may give an example with some dummy data and state the layout you want as output?
Alternative, you may put your question to MrExcel forum http://www.mrexcel.com/forum/forum.php (my all time favourites), where lots of Excel experts are willing to help unconditionally. 🙂
Cheers,
LikeLike