Excel Tips to Impress your Boss #2 SUMIFS
If you read my last post you now know all there is to know about SUMIF. Which leads into a much more powerful function SUMIFS! Thats right SUMIFS.
This function is kinda like a lot of SUMIF functions. Here you specify your sum range (where you are adding numbers together) but instead of having one criteria and range (what you want and where you look for it) you can have as many as your heart desires …. in fact you can have more than that!
This is the way to fill in the formula for 2 criteria:
=SUMIFS ([Sum Range],Range1,Criteria1, Range2, Criteria2)
[Sum Range] – Where you are adding numbers together
Range1 – Where you look for Criteria1
Criteria1 – What you are looking for
Range2 – Where you look for Criteria2
Criteria2 – What you are looking for
Last week my wife kicked me out the house since I spent too much money on my car. So I decided to return some of the items I purchased for my car in order to get back into her good books! Sounds like a good opportunity to use SUMIFS!
From the above excel extract you can see that column A contains who I purchased the items for, column C contains whether I returned the item or not and column D contains the price for each item. What I want to know is the cost of what I kept for my Wife compared to what I kept for my Car.
So how did I use SUMIFS here? Lets look at the cost of what I kept for my Wife. The formula looks a bit like this =SUMIFS(D2:D9,A2:A9,”Wife”,C2,C9,”Kept”) and
- D2:D9 was my Sum Range i.e What I wanted to add together
- A2:A9 was my first Range i.e Where I looked for my first criteria
- “Wife” was my first criteria
- C2:C9 was my second Range i.e Where I looked for my second criteria
- “Kept” was my second criteria
Now the most important thing to note is your are not limited to two ranges and criteria, you can have much more! A more general formula for this SUMIFS would be
=SUMIFS ([Sum Range], Range1, Criteria1, Range2, Criteria2, . . . . . , RangeN, CriteriaN)
or in other words
=SUMIFS( What you want to add together , where to look first , first criteria , where to look second , second criteria , …… , where to look the N’th time , N’th criteria)
Even more important is that I got let back into the house….well onto the couch…. but its a start! I really shouldn’t have returned the flowers.
I hope this helped you with your excel experience which your sure to excel in. This is goodbye from me, for now, and as always ‘Remember if you’re stuck in excel …Google is your best friend’
Authored by “Excel Aidan” a self confessed Excel fanatic and near-do-well funny man. See Aidan’s profile here and email firstname.lastname@example.org if you have a suggestion for our next excel topic.
At Barden we invest our resources to bring you the very best insights on all things to do with your professional future. Got a topic you would like us to research? Got an insight you would like us to share with our audience? Drop us a note to email@example.com and we will take it from there!