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:

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