Explanation
In this example, the goal is to count the number of cells in column D that contain dates that are between two variable dates in G4 and G5. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. For convenience, the worksheet contains two named ranges : date (D5:D16) and amount (C5:C16). The named range amount is not used to count dates, but can be used to sum amounts between the same dates, as seen below.
Note: Excel dates are large serial numbers so, at the core, this problem is about counting numbers that fall into a specific range. In other words, SUMIFS and SUMPRODUCT don’t care about the dates, they only care about the numbers underneath the dates. If you like, you can see the numbers underneath by temporarily formatting the dates with the General number format .
COUNTIFS function
The COUNTIFS function is designed to count cells that meet multiple conditions. In this case, we need to provide two conditions: (1) the date is greater than or equal to G4 and (2) the date is less than or equal to G5. COUNTIFS accepts conditions as range/criteria pairs, so the conditions are entered like this:
date,">="&G4 // greater than or equal to G4
date,"<="&G5 // less than or equal to G5
The final formula looks like this:
=COUNTIFS(date,">="&G4,date,"<="&G5)
Note that the logical operators “>=” and “<=” must be entered as text and surrounded by double quotes. This means we must use concatenation with the ampersand operator (&) to join the operators to the dates in cell G4 and cell G5. This syntax is specific to a group of eight functions in Excel .
SUMPRODUCT function
This problem can also be solved with the SUMPRODUCT function which allows a cleaner syntax:
=SUMPRODUCT((date>=G4)*(date<=G5))
This is an example of using Boolean algebra in Excel. Because the named range date contains 12 dates, each expression inside SUMPRODUCT returns an array with 12 TRUE or FALSE values. When these two arrays are multiplied together, they return an array of 1s and 0s. After multiplication, we have:
=SUMPRODUCT({0;0;1;1;1;1;0;0;0;0;0;0})
SUMPRODUCT then returns the sum of the elements in the array, which is 4.
Sum amounts between dates
To sum the amounts on dates between G4 and G5 in this worksheet, you can use the SUMIFS function like this:
=SUMIFS(amount,date,">="&G4,date,"<="&G5)
The conditions in SUMIFS are the same as in COUNTIFS, but SUMIFS also accepts the range to sum as the first argument . The result is $630.
To sum the amounts for dates between G4 and G5, you can use SUMPRODUCT like this:
=SUMPRODUCT((date>=G4)*(date<=G5)*amount)
Here again, the conditions are the same as the original SUMPRODUCT formula above, but we have extended the formula to multiply by amount . This formula simplifies to:
=SUMPRODUCT({0;0;1;1;1;1;0;0;0;0;0;0}*{180;120;105;100;220;205;225;140;180;200;240;235})
The zeros in the first array effectively cancel out the amounts for dates that don’t meet criteria:
=SUMPRODUCT({0;0;105;100;220;205;0;0;0;0;0;0})
and SUMPRODUCT returns $630 as a final result.
Explanation
In this example, the goal is to count numbers that fall within specific ranges. The lower value comes from the “Start” column, and the upper value comes from the “End” column. For each range, we want to include both the lower value and the upper value. For convenience, the numbers being counted are in the named range data (C5:C16). This problem can be solved with both the COUNTIFS function and the SUMPRODUCT function, as explained below.
COUNTIFS function
In the example shown, the formula used to solve this problem is based on the COUNTIFS function , which is designed to count cells that meet multiple criteria. The formula in cell G5, copied down, is:
=COUNTIFS(data,">="&E5,data,"<="&F5)
COUNTIFS accepts criteria in range/criteria pairs. The first range/criteria pair checks for values in data that are greater than or equal to (>=) the “Start” value in column E:
data,">="&E5
The second range/criteria pair checks for values in data that are less than or equal to (<=) the “End” value in column F:
data,"<="&F5
Because we supply the same range ( data ) for both criteria, each cell in data must meet both conditions in order to be included in the final count. Note in both cases, we need to concatenate the cell reference to the logical operator . This is a quirk of RACON functions in Excel , which use a different syntax than other formulas.
As the formula is copied down column G, it returns the count of numbers that fall in the range defined by columns E and F.
SUMPRODUCT alternative
Another option for solving this problem is the SUMPRODUCT function with a formula like this:
=SUMPRODUCT((data>=E5)*(data<=F5))
This is an example of using Boolean logic . Because there are 12 values in the named range data , each logical expression inside SUMPRODUCT generates an array that contains 12 TRUE and FALSE values. The expression:
data>=E5
returns:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
And the expression:
data<=F5
returns:
{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}
When these two arrays are multiplied together, the math operation causes TRUE values to be coerced to 1 and FALSE values to be coerced to zero. The result is a single array of 1s and 0s like this:
=SUMPRODUCT({1;0;0;0;1;0;0;0;1;0;1;0})
Each 1 corresponds to a number in data that meets both conditions and each 0 represents a number that fails at least one condition. With only a single array to process, SUMPRODUCT returns the sum of the numbers in the array, 4, as a final result.
Video: Boolean logic in Excel
Benefits of standard syntax
The nice thing about SUMPRODUCT is that it will handle array operations natively (no need to enter with control + shift + enter) and will accept the more standard syntax for logical expressions seen above. The standard syntax is more useful in other modern formulas. For example you could use the same exact logic with the FILTER function to return the actual numbers that meet these same conditions:
=FILTER(data,(data>=E5)*(data<=F5))
Becomes:
=FILTER(data,{1;0;0;0;1;0;0;0;1;0;1;0})
And FILTER returns the numbers greater than or equal to 70 and less than or equal to 79:
{79;77;75;70}
Video: Basic FILTER function example
