To get the sum of values in a range of cells, we can use the SUMIF() function. The syntax is like this:
SUMIF(range, criteria, sum_range)
In this example we want to get the sum of sales from a region (“North”):
As you can see on the formula bar, on cell E10 we have the following formula:
=SUMIF(B3:B8,”North”,E3:E8)
This will check on cells B3:B8 for the North region and will sum the values that correspond on cells E3:E8.
But if we wanted to get the sum of the North region for only a customer? Before Excel 2007, we needed to use the SUMPRODUCT() function. Now we have a new function in Excel that is called SUMIFS(). With this new function, we can have 2 or more conditions. Here’s the syntax:
SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])
The first argument is the range of cells to be summed. Then you should specify pairs of arguments that consist on the range to be check and the value to be match on that range. So, here’s an example of the sum of sales for the North region for customer “TMA, Inc”:
If you check the formula bar, our formula will sum the values on cells E3:E8 that have the region North on cells B3:B8 and the customer “TMA, Inc” on cells C3:C8.
When moving from SUMIF to SUMIFS, you should pay attention because the syntax is in a different order.
