Sumifs Function

Are you tired of creating helper columns to be able to fetch data with sumif function? If you need to sum of a list of values in a range based on multiple conditions, you can do this easily with sumifs function. Sumifs function is a steroid taking version of good old sumif function.

Sumifs function is very similar to sumif function. Only difference is power. While sumif function can sum values in a range based on one criteria, sumifs function can sum values in a range based on multiple criteria. This is especially useful for getting rid of helper columns and creating fixed ranges that changes dynamically.

Here is how Sumif Function works:

Lets assume that you have an unsorted list that contains customers and monthly sales revenues which looks like:

You need to get total sales revenue for customer1 for 2nd month. For this task sumif can not cover you. Since sumif function can sum values in a list based on a single criteria, you need a more powerful function. This is where sumifs function comes into play.  Sumif function is almost same as sumif function but it lets you sum your values based on multiple conditions (up to 127 !).

To get 2nd month total sales for customer1, you need to type following formula:

=SUMIFS(D3:D13;B3:B13;”customer1″;C3:C13;2)                            (which will result   $ 28,996.00 )

If you are uncertain about how to do it or just lazy, you can also use formula editor:

sumifs_function

For more tutorials like this, you may visit formulas category.

You may also like...

3 Responses

  1. Brad S. says:

    Is there a faster way to do SUMIFS? I have a spreadsheet that I have many calculations on and when I add the SUMIFS to the mix the thing is almost unusable. I guess I could disable the formula auto-refresh, but I’m in the habit of keeping it on.

    Thanks,

    Brad

    • melih says:

      Greeetings.

      It is hard to say anything accurate without seeing your formulas/workbook. But first thing that comes into mind is coding a custom function in vba. You may also check Sumproduct function to see if it fits your case.

      Unfortunately I’m on vacation and won’t be back untill tuesday. If you e-mail me your workbook, I can check it when I return.

  1. February 13, 2016

    […] This formula fetches result values based on department and KPI values selected on the pivot table above and month name in first column. You can see how to use this function from here SUMIFS FUNCTION. […]

Leave a Reply

Your email address will not be published. Required fields are marked *