Exclude Values in Sumif
Have you ever needed to exclude values in sumif function? This article will show you how to do it.
Sumif is a very useful function that sums values based on criteria. Sumif requires you to
- Specify a range to be searched for the criteria,
- A criteria
- A range to sum values based on that criteria.
Then it sums values based on the criteria you specified. You can see more clearly in the picture below.
In this example, we are taking a sum of number of employees based on their country of origin.
If you want total number of employees excluding a country of origin, you can exclude values in sumif to do that. Here is how it works:
In this example we changed our criteria as “<>Spain” (which means “not equal to Spain”). Therefore sumif function summed column B excluding values that has Spain as country of origin.
This trick also works with wildcard characters that allows you to exclude multiple countries with one criteria. If you modify your criteria as “<>I*” (which means “not starting with letter I”), sumif function exclude countries starting with letter “I” (Italy and India).
Here is how to use this trick:
Everything mentioned here also works for SUMIFS Function. Sumifs function is a very powerful version of sumif function, which allows you to sum values based on multiple criteria. To learn about sumifs function, you can check the link below:
To download the example file to see the examples above in action, you can use the link below:
