In Excel, we often have multiple dimensional dataset wherein there are more than 1 attribute of the data and corresponding to it, we have certain numerical values.
Lets see an example of such a dataset below. Look at the data carefully.
In the above dataset, we have the year labels in column B, the quarter labels in column C and the value of the Agriculture & Allied Activities sector in column D.
So there are 2 main attributes in the above dataset i.e. Years and Quarters. And against each of those two attributes we have the GDP contribution value of the Agriculture and Allied Activities sector.
Clear so far? Good.
Now, let us say, we want to calculate for any given year, what is the total of the quarterly values for Agriculture sector?
So if the user wants to calculate for the year 2004-05, the total of Agriculture sector values are=SUM(D6:D9) i.e sum of (1357+1089+1724+1484) = 5654.
And for the year 2005-06, the total would be=SUM(D10:D13), i.e. sum of (1394+1130+1858+1563) = 5945.
This is pretty straight forward isn’t it? Yes, it is.
If you notice carefully, we are adding up the different values corresponding to the particular year only. That is we are making a conditional SUM such that it only adds up those years where the year matches the desired value i.e. 2004-05 or 2005-06 or 2006-07.
This is where the SUMIF function comes in handy in Excel. It allows us to write a formula in one single shot that can add up only those values where a certain condition is met. In this case, we want to add up the Agriculture and Allied Activities sector values corresponding to a certain year only.
Let us see how we can do that. Given any specific year, how do we write the SUMIF formula to get the total of the Agriclture and allied activities sector value.
The SUMIF function requires the following three elements as part of its syntax:
So, the final formula will become = SUMIF(RANGE,CRITERIA,[SUMRANGE])
For the example, we are working on if we want to find out the value for the year “2006-07”, then we write it as = SUMIF (B6:B17, “2006-07”, D6:D17)
This formula will add up the values for the year 2006-07 in the entire range of Agriculture and allied sector values, i.e = (1448+1169+1932+1642) = 6192 as the result of the same.
[Aside] If you want the results for any year, just mention it within quotation marks i.e. “2004-05” or “2005-06” or “2006-07” etc in the criteria portion of the formula. It will accordingly add up values meeting that particular criterion only.
Now, while the formula is pretty straightforward, it requires a bit of practice at your end as well. In our experience, there are a few IMPORTANT thingsto make note of while writing this formula for our real work. Some of them are as follows which we should remember:
So as you can see, the SUMIF functionality can be very useful for finding conditional totals in a dataset. As long as there is one condition to be met, the SUMIF is a great resource to use. Check it out yourself.