This Excel tutorial shows you how to use multiple rules in a sumif by using the SUMIFS function.
The sumif function allows you to sum a column of data based on a condition. You might decide you want to sum column B but only whilst column A meets a certain criteria. Let’s take an example where you have gender in column A (M= Male ,F= Female) and sales in column B. If you wanted to sum sales for women only you could use the following formula:
What does this mean? Well, we are looking at column A (Gender) and if it matches “F” then we sum column B. This is the SUMIF function in it’s most simple form.
But what if you have multiple conditions you want to test against? Then the SUMIFS function is the answer. The SUMIFS function allows you to test against multiple criteria. You could check that Gender is “M” and Age is < 30 years old for example.
Our data below shows this example. Column A has gender, column B now has age and column C has our sales that we’d want to add up. The SUMIFs formula works as follows:
=SUMIFS(SumRange, ConditionRange1, Criteria1, ConditionRange2, Criteria2,...)
In our example below our SUMIFS for sales by men under 30 is: