Excel Sumif and Sumifs are a must function for any Excel user. If you want to sum cells in a clever way and be able to quickly adjust that way easily, these functions will be very useful for you.
Excel Sumif, Sumifs example
In order to demonstrate the capabilities of these functions I will use a US cities and states database I downloaded from the internet. The database has ~900 rows. This is a small scale print screen of the database :
In column N you can see an example of Sumif. Based on the US database above, I wanted to sum the population of each state. Sumif only allows one condition to be checked for the summing, sumifs allow you to define many conditions. In column O you can see an example of sumifs with many conditions – not only am I summing the population by state,I am also including cities with a name shorter than 10 characters and the population is more than 300K.
Excel Sumif, Sumifs code breakdown for easy copying
Column N = Sumif($C:$C,$J14,$F:$F)
Column O = Sumifs($F:$F,$C:$C,$J14,$H:$H,”<10″,$F:$F,”>300000)
Detailed explanation of the function Sumif
1st argument – Range where the criteria will be checked
2nd argument – Value to be checked in the range
3rd argument – Range to be summed for each row where there is a match
Detailed explanation of the function Sumifs
1st argument – Range to be summed for each row where there is a match
2nd argument – Range where the criteria will be checked
3rd argument -Value to be checked in the range
Each new argument couple will be the range and value based on the 2nd and 3rd example.
Sumif – for simple sums, Sumifs for complicated sums these formulas are a must of any Excel user. Once you start using them, you’ll never look back! Here are some more functions below.