Exceljet

Quick, clean, and to the point

Sum by month ignore year

Excel formula: Sum by month ignore year
Generic formula 
=SUMPRODUCT((MONTH(dates)=month)*amounts)
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, ignoring year.

How this formula works

This data set contains over 2900 records, and the formula above uses two named ranges:

dates = B5:B2932
amounts =  E5:E2932

Inside the SUMPRODUCT function, the MONTH function is used to extract the month number for every date in the data set, and compare it with the number 3:

(MONTH(dates)=3)

If we assume a small data set listing 3 dates each in January, February, and March (in that order), the result would be an array containing nine numbers like this:

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

where each number is the "month number" for a date.  When the values are compared to 3, the result is an array 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. If we assume all nine amounts are equal to 100, the operation looks like this:

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

Notice the math operation changes the TRUE FALSE values into ones and zeros. After multiplication, we have a single array in SUMPRODUCT:

=SUMPRODUCT({0;0;0;0;0;0;100;100;100})

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

Finally, SUMPRODUCT returns the sum of all items – 300 in the abbreviated example above, and 25,521 in the screenshot with actual data. 

Count by month ignoring year

To get a count by month ignoring year, you can use SUMPRODUCT like this:

=SUMPRODUCT(--(MONTH(dates)=3))

Average by month ignoring year

To calculate and average by month ignoring year, you combine the two SUMPRODUCT formulas above like this:

=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.