Introduction to Excel SUMIFS Formula
If SUMIF formula is like a regular knife that you can use to slice up your data and get what you want, then SUMIFS is like a Swiss army knife.
What is SUMIF?
We all know that SUMIF formula helps to findout the sum of all values meeting a condition. So, if you have data like below
and you want to find out how many Spit Bombs you sold (spit bombs, really?) then you can use SUMIF like this:
But as you guess, SUMIF has a serious limitation. It can only process one condition at a time. So if you want to find out how many Spit Bombs are sold in North, you have to,
- Drive down to North and meet the victims to take count of spit bombs sold
- Use array formulas to SUM based on multiple conditions
- Use SUMPRODUCT formula
While the first option takes a lot of time, the other two involve writing complex formulas.
So What is SUMIFS Formula?
SUMIFS is the plural version of SUMIF. Using SUMIFS you can find the sum of values in your data that meet multiple conditions.
So, to get the sum of all the spit bombs sold in North, we just write,
Similarly to find the podgun sales in East, just write,
How Does SUMIFS Formula work?
SUMIFS formula takes a range for summing the values and at least one criteria range and criteria. You can specify as many as 127 conditions for summing your data.
Imagine asking “how many spit bombs Hansolo sold in North region of Planet Naboo between long long ago and long ago that resulted in more than 25% profits?” and getting an instant answer.
The beauty of SUMIFS formula is that it works with wildcards too, just like its siblings – SUMIF and COUNTIF. So you can write formulas like,
=SUMIFS(D3:D16,B3:B16,”Spit Bomb”,C3:C16,”*th”) to get sum of spit bombs sold in North and South.
So what is the catch?
While SUMIFS seems like all butterflies and berries, there is a catch. It works only in Excel 2007 and above. But with Excel 2010 in the horizon, I have high hopes that finally everyone will be able to enjoy the SUMIFS formula.
Just like SUMIFS, there is COUNTIFS and AVERAGEIFS too in Excel 2007+. I am sure you already know what they mean.
|How to Select Right Chart for your Data||Budget vs. Actual Profit Loss Report using Pivot Tables|