How to use SUMIF formula with multiple conditions in Excel
One way to use two or more conditions in the SUMIF function is by adding or subtracting the results of several SUMIF functions. If two conditions apply to the same range of values, this method is quite effective. However, if the ranges are different, this method becomes more complex, as you need to ensure that duplicate values are not counted twice in the final calculations.
SUMIF function with multiple conditions in the same range in Excel
Let's assume we need to sum numerical values between the dates of June 23rd and June 29th, inclusive. The start and end dates are entered in corresponding cells of an auxiliary table to create query conditions for filtering values from the main table. The image below shows the initial values and conditions for summing:
=SUMIF(A2:A12,"<="&E3,B2:B12)-SUMIF(A2:A12,"<"&E2,B2:B12)
This technique for summing with multiple conditions using the SUMIF function is based on subtraction. In this formula, the first SUMIF function returns the sum of values for dates earlier than or equal to the end date in cell E3 (in this case, June 29th). The "less than" operator is combined with the ampersand (&) and a reference to cell E2 (the start date).
If the formula only contained the first function =SUMIF(A2:A12,"<="&E3,B2:B12), the result would be 5721.
However, we need to sum the numbers between June 25th and June 29th, not between June 20th and June 29th. To do this, we subtract from the first result of 5721.00 the sum of numbers earlier than June 25th, which is 3,726.50.
We achieve this by using the SUMIF function again but with a slightly modified second argument: SUMIF(A2:A12,"<"&E2,B2:B12).
Now, let's check the difference between the calculations of the two parts of the formula using subtraction:
Summing the numerical values corresponding to dates earlier than or equal to the end date (June 29th) and then subtracting from this result the sum of all numerical values corresponding to dates earlier than the start date (June 25th) allows us to get the sum of all numbers between the specified dates in the query table.
Example of SUMIFS function with multiple conditions in Excel
If you're using Excel 2010 or later, it’s better to use the SUMIFS function to sum values with multiple conditions. It calculates the correct result faster with fewer system resources. Additionally, the newer function is more intuitive than the previously described subtraction method. The formula for SUMIFS:
=SUMIFS(B2:B12,A2:A12,"<="&E3,A2:A12,">="&E2)
This function selectively sums only those cell values in the range B2:B12 that meet the conditions specified in the two argument pairs. The first condition "<="&E3 is identical to the condition in the first SUMIF function from the previous example. The second condition ">="&E2 restricts the calculation by excluding all numbers before the start date.
Example of SUMPRODUCT function for summing a range with multiple conditions
In both old and new versions of Excel, you can use the SUMPRODUCT function. Its efficiency is higher than the technique in the first example, but it lags behind both in performance and speed compared to the newer SUMIFS function. The formula looks like this:
=SUMPRODUCT((A2:A12<=E3)*(A2:A12>=E2)*(B2:B12))
As seen in the image, we got a similar calculation result. How SUMPRODUCT works in this formula is described in detail in the article: Formulas for summing with multiple conditions in Excel.
These calculations are based on multiplying logical values and numbers in column B2:B12. The results of the first two logical expressions return TRUE=1 and FALSE=0. If any of the multipliers equals zero, the result is 0. For example, how this formula reads the first row:
TRUE*FALSE*876.50=0
That is:
1*0*876.50=0
If both logical expressions return TRUE, the current cell in column B with the sales figure is multiplied by ones, and then all the found values are summed up. To illustrate the example, we’ll create a table showing how SUMPRODUCT sees the current situation:
Download examples of using SUMIF with multiple conditions in Excel formulas
Despite the fact that the SUMPRODUCT function is older and not as fast or convenient as SUMIFS, it will always be supported by new versions of Excel to allow opening files from older versions without calculation errors in formulas. Therefore, it’s worth understanding how it works.