Sum by month ignore year

Generic formula

=SUMPRODUCT((MONTH(dates)=month)*amounts)

Related formulas

Sum by month

Summary count by month with COUNTIFS

Explanation

To sum data by month, ignoring year, you can use a formula based on the SUMPRODUCT and MONTH functions.

In the example shown, the formula in H6 is:

=SUMPRODUCT((MONTH(dates)=3)*amounts)

The result is a total of all sales in March, regardless of year.

How this formula works

This formula uses two named ranges:

dates = B5:B2932amounts = E5:E2932

Inside the SUMPRODUCT function, the MONTH function is used to extract the month number for every date in the data set. An abbreviated version of the result is an array that might look like this:

{1;1;1;2;2;2;3;3;3}

Each value is compared to 3 (the month number for March) to get a result like this:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

This array is then multiplied by the amount values associated with each March date. The math operation changes the TRUE FALSE values into ones and zeros, so the operation looks something like this:

 {0;0;0;0;0;0;1;1;1} * {100;100;100;100;100;100;100;100;100}

Where 100 is just a placeholder for any amount. The result is a single array like this:

 {0;0;0;0;0;0;100;100;100}

Note the only surviving amounts are associated with March, the rest are zero.

SUMPRODUCT then sums up the items in the array and returns a result, 300 in the abbreviated example above, and 25,521 in the screenshot.