Hits: 5

# (Excel examples for Beginners)

In this end-to-end excel example, you will learn – How to Sum by month ignore year in Excel.

#### Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.

Western Australian Center for Applied Machine Learning & Data Science – Membership

# How to Sum by month ignore year in Excel

### 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))

#### Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.

Western Australian Center for Applied Machine Learning & Data Science – Membership