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.
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.
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.
What about if you wanted to exclude only those two criteria (instead of including them) – for example, the total number of sales except for Customer 1 in the 2nd month?