Explanation
In this example, the goal is to sum numeric values by month while ignoring the year that contains the date. The solution below is based on the SUMPRODUCT function, the MONTH function, and Boolean algebra . For convenience, amount (C5:C16) and date (B5:B16) are named ranges .
Basic concept
The basic concept in this formula is to extract just the month number from all dates and test this number against the month number of interest. For example, to extract the month number of the dates in date, we can use the MONTH function like this:
MONTH(date)
Because the named range date contains 12 dates, the result from MONTH is an array with 12 numbers like this:
{1;2;2;3;3;3;1;2;2;3;3;3}
These twelve numbers correspond to the month numbers of the dates seen in column B. If we want to test these numbers for dates in January (which is the first month in the year), we can write a formula like this:
=MONTH(date)=1
This formula returns an array with 12 TRUE and FALSE values like this:
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
Notice there are two TRUE values in this array, which correspond to the two dates that occur in January: one in 2022 and one in 2023. All remaining values are FALSE, since other dates do not occur in January.
To use this array of Boolean values to sum amounts in January, we can write a formula like this:
=SUMPRODUCT((MONTH(date)=1)*amount)
After the first expression runs, we have the array of TRUE and FALSE values we looked at above:
=SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}*amount)
Next, the math operation of multiplying the two arrays together automatically coerces the TRUE and FALSE values into 1s and 0s, which we can visualize like this:
=SUMPRODUCT({1;0;0;0;0;0;1;0;0;0;0;0}*amount)
Now you can see how the logic works. When the two arrays are multiplied together, the 1s return the corresponding value in amount , while the 0s “cancel out” the other amounts. We are left with a single array in SUMPRODUCT like this:
=SUMPRODUCT({100;0;0;0;0;0;125;0;0;0;0;0})
With just one array to process, SUMPRODUCT sums the array and returns 225 as the final result for January. By altering the month number, we can do the same thing for other months:
=SUMPRODUCT((MONTH(date)=1)*amount) // January
=SUMPRODUCT((MONTH(date)=2)*amount) // February
=SUMPRODUCT((MONTH(date)=3)*amount) // March
In each case, the year values of the dates being tested are completely ignored.
Dynamic month
As seen above, we can hardcode month numbers into the formula and get correct results, but how can we make the formula dynamic, so that it will automatically apply the correct month number for each month seen in column F? In the worksheet, the values in F5:F7 are simply text values like “Jan”, “Feb”, and “Mar”. One way to get a month number from a month name is to concatenate the month name to the number 1, and feed the result into MONTH like this:
=MONTH(E5&1) // returns 1
The result inside of MONTH is the string “Jan1”, which Excel interprets as the date January 1 of the current year, and MONTH returns 1. We can do the same thing with E6 and E7:
=MONTH(E6&1) // returns 2
=MONTH(E7&1) // returns 3
For a more detailed explanation, see this example .
Putting it all together
The last step is to combine the ideas above into one formula:
=SUMPRODUCT((MONTH(date)=MONTH(E5&1))*amount)
This is the formula in F5 of the worksheet shown. As the formula is copied down, the month changes at each new row, and SUMPRODUCT calculates the sum of amounts for each month, ignoring year values.
Count by month ignoring year
Using the same ideas explained above, you can get a count by month like this:
=SUMPRODUCT(--(MONTH(date)=MONTH(E5&1)))
In this formula, we use a double negative (–) is used to coerce TRUE and FALSE values to 1s and 0s. This step is necessary because we don’t have a math operation doing this conversion automatically.
Video: Boolean operations in array formulas
Explanation
In this example, the goal is to construct a formula that will subtotal the amounts in column D by client and month as seen in the range G5:I8. A big part of the problem is to set up the proper references so that the formula can be entered once, and copied throughout G5:I8. The solution explained below is based on the SUMIFS function.
Summary table setup
The first step in solving this problem is creating the summary table seen in the range F4:I8. The values in F4:F9 are text values. However, the month names in G4:I5 are actually valid Excel dates , formatted with the custom number format “mmm”:
SUMIFS solution
The SUMIFS function can sum values in ranges based on multiple criteria. Multiple criteria are entered in range/criteria pairs like this:
=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)
To sum amounts by client and month with SUMIFS, we will need to enter three criteria
- Client = client in column F
- Date >= first of month (from date in row 4)
- Date <= end of month (from date in row 4)
The first step in configuring SUMIFS is to enter the sum_range , which contains the values to sum in column D. For sum_range , we use the named range amount :
=SUMIFS(amount
Next, we need to enter the first range/criteria pair to target values in column B:
=SUMIFS(amount,client,$F5
Notice F5 is a mixed reference , with the column locked and the row relative. This allows the row to change as the formula is copied down the table, but the client name in column F does not change as the formula is copied to the right.
Next, we need to enter the second range/criteria pair, which is used to target dates that are greater than or equal to the month in G4. For the range, we use the named range date . For criteria, we use the greater than or equal to (>=) operator concatenated to the value in G4:
=SUMIFS(amount,client,$F5,date,">="&G$4
Notice G$4 this is another mixed reference, this time with the row locked. This allows the column to change as the formula is copied across the table, but keeps the row number fixed as the formula is copied down. The concatenation with an ampersand (&) is necessary when building criteria that use a logical operator and a value from another cell, because the SUMIFS function is in a group of eight functions that split criteria into two parts.
Finally, we need to enter the third range/criteria pair to check dates against the last day of the month. For the range, we again use the named range date . For criteria, we use the less than or equal to (<=) operator concatenated to the EOMONTH function:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
We use the EOMONTH function to get the last day of the month of the date in G4. As before, we need to concatenate the result from EOMONTH to the logical operator. Again, the reference to G$4 is mixed to keep the row from changing.
As the formula is copied into the range G5:I8, the SUMIFS function returns a sum for each client and month.
Pivot Table solution
A pivot table would be an excellent solution for this problem, because it can automatically group by month without any formulas at all. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables .
