How to use SUMIFS in Excel.

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: =SUMIF(A:A,"F",B:B)

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.

Excel Sumif Example

Excel Sumif Example

 

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,...)
SumRange is the column we want to sum, in this case column C, Sales.  ConditionRange1 is the first range of cells we test against.  In this instance we will test column A, Gender.  Criteria1 is the test for the first column, in this case we want to test that it equals “M”.  You can then continue this process for more conditions.  The general rule is that you specify the range where we want to test a condition.  Then we declare the criteria.  In our example we first test against gender, then we test against age.

In our example below our SUMIFS for sales by men under 30 is:

=SUMIFS(C:C,A:A,"M",B:B,"<30")
This tells us that 233 sales have been made by men under the age of 30.  Sumifs remain a good alternative to pivot tables if you fancy avoiding large file sizes due to too many pivot tables.

 

How to do SUMIFS in Excel

How to do SUMIFS in Excel

2 comments to How to use SUMIFS in Excel.

Leave a Reply

  

  

  


5 − = three

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>