Explanation
In this example, the goal is to sum Total when the corresponding Color is either “Red” or “Blue”. For convenience, all data is in an Excel Table named data . This is a tricky problem, because the solution is not obvious. The go-to function for conditional sums is the SUMIFS function . However, when using SUMIFS with multiple criteria, all conditions must be TRUE. This means that multiple conditions are joined with AND logic and there is no direct way to apply conditions with OR logic. One solution is to use an array constant with SUMIFS, then sum the results with the SUM function. Another option is to use SUMIFS twice.
SUMIFS + SUMIFS
Before looking at the more advanced option shown in the example, it’s important to note that we can solve this problem with two calls to SUMIFS like this:
=SUMIFS(data[Total],data[Color],"red")+
SUMIFS(data[Total],data[Color],"blue")
The first SUMIFS sums Total where the color is “Red”, and the second SUMIFS sums Total where the color is “Blue”. By adding the two functions together in one formula, we effectively get a sum of Total where the color is either “Red” or “Blue”.
SUMIFS + array constant
A more elegant solution is to give the SUMIFS function more than one value for the criteria, in an array constant . To do this, construct a normal SUMIFS formula, but supply criteria in array syntax like this:
{"red","blue"} // array constant
Placing the array constant inside SUMIFS as criteria1 , we have:
SUMIFS(data[Total],data[Color],{"red","blue"})
In this configuration, SUMIFS will return two sums: one for totals where the color is “Red” and one for totals where the color is “Blue”. These results will be returned in an array like this:
{86,119} // result from SUMIFS
In the latest version of Excel you will see these results spill onto the worksheet into two cells. However, we don’t want two results on the worksheet (we want a single result) so we wrap the SUMIFS function inside the SUM function like this:
=SUM(SUMIFS(data[Total],data[Color],{"red","blue"}))
Now SUMIFS will return the array to SUM:
=SUM({86,119}) // returns 205
and the SUM function will return the final result, 205.
Criteria as reference
You can also supply criteria as a cell reference instead of an array constant. For example, with “Red” in cell A1 and “Blue” in cell A2, you can use a formula like this:
=SUM(SUMIFS(data[Total],data[Color],A1:A2))
In the latest version of Excel, this formula will work as-is, with no special handling. In Legacy Excel , you will need to enter as an array formula with control + shift + enter.
SUMPRODUCT alternative
You can also use the SUMPRODUCT function to sum cells with OR logic with a formula like this
=SUMPRODUCT(--ISNUMBER(MATCH(data[Color],{"red","blue"},0))*data[Total])
This formula uses the MATCH function and the ISNUMBER function to create a Boolean array that is then multiplied by data[Total] to get a final result. This is a flexible approach that works nicely in situations where SUMIFS can’t be used.
Note: in the current version of Excel you can use the SUM function instead of SUMPRODUCT. See Why SUMPRODUCT?
Explanation
In this example, the goal is to calculate total amounts in column C that occur in the last 30 days, based on a current date of December 30, 2022. There are three basic approaches to solving this problem: (1) a traditional approach based on the SUMIFS function , (2) a more flexible approach that uses Boolean logic and the SUMPRODUCT function , a modern approach based on the FILTER function . Each approach is explained below.
Excel date logic
To understand the logic of the formulas explained below, the first step is to understand Excel’s date system, where dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on. More recent dates are much larger numbers. For example, January 1, 1999 is 36161, January 1, 2010 is 40179, and January 1, 2020 is 43831. Because dates are just numbers, you can easily perform arithmetic on dates. For example, with January 1, 2020 in cell A1:
=A1+10 // Jan 11, 2020
=A1-10 // Dec 22, 2019
This also means we can use the TODAY function to return the current date, then subtract 30 days to get a date 30 days earlier. For example, as I write this, the current date is December 30, 2022 so:
=TODAY() // Dec 30, 2022
=TODAY()-30 // Nov 30, 2022
Therefore, to test if a date in cell A1 occurs within the last 30 days, we can use a formula like this:
=A1>=TODAY()-30
The above will return TRUE if the date is within the last 30 days of the current date and FALSE if not.
SUMIFS function
As mentioned above, the traditional way to solve a problem like this is to use the SUMIFS function. The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria . To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The generic syntax for SUMIFS looks like this:
=SUMIFS(sum_range,range1,criteria1) // 1 condition
where sum_range contains the values to sum, range is the cell range to test, and criteria1 is the criteria to apply to range . To solve this problem, we begin with the sum_range , which is C5:C16:
=SUMIFS(C5:C16,
Next, we add the range, which contains the dates in B5:B16:
=SUMIFS(C5:C16,B5:B16,
Finally, we add the criteria:
=SUMIFS(C5:C16,B5:B16,">="&TODAY()-30)
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function and Boolean logic like this:
=SUMPRODUCT((B5:B16>=TODAY()-30)*C5:C16)
In this case, we are multiplying all values in C5:C16 by an expression designed to “zero out” values that should not be included in the final sum:
(B5:B16>=TODAY()-30)
The result from the expression above is an array of TRUE and FALSE values like this:
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
The TRUE values in this array represent dates that occur in the last 30 days, based on a current date of December 30, 2022 returned by the TODAY function. These are the first five dates in the data as shown.
Next, the array above is multiplied by the values in C5:C16. In Excel, TRUE and FALSE values are automatically coerced to 1s and 0s by any math operation, so the multiplication step converts the arrays above to ones and zeros. We can visualize this operation inside of SUMPRODUCT like this:
=SUMPRODUCT({1;1;1;1;1;0;0;0;0;0;0;0}*C5:C16)
After multiplication, we have just a single array:
=SUMPRODUCT({5550;7450;1000;725;7150;0;0;0;0;0;0;0})
Notice that values associated with dates that are not within the past 30 days have been “zeroed out”. With just one array to process, SUMPRODUCT returns the sum of all items in the array: 21,875.
Note: Why would you use SUMPRODUCT instead of SUMIFS? The biggest reason is flexibility. Unlike SUMPRODUCT, SUMIFS requires a cell range ; you can’t use an array. This means SUMIFS won’t work in formulas that need to manipulate values before conditional logic is applied.
FILTER function
In the latest version of Excel, you can solve this problem with the FILTER function like this:
=SUM(FILTER(C5:C16,B5:B16>=TODAY()-30,0))
In this formula, we use the same logic explained above to target dates in the past 30 days:
B5:B16>=TODAY()-30 // last 30 days
The expression above returns an array of TRUE and FALSE values like this:
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
And this array is used to filter the values in C5:C16. The result from FILTER is then returned directly to the SUM function:
=SUM({5550;7450;1000;725;7150})
SUM then returns a final result of 21,875.
