# SUMIF using the MONTH of the DATE in the criteria range

1. ## SUMIF using the MONTH of the DATE in the criteria range

I have a table where one column is populated with dates and the next column is populated with numbers.

I want to sum the numbers IF the MONTH of the DATE is the same as the MONTH of TODAY.

For instance:
 A B 1 January 1 2 2 March 5 7 3 December 8 8 4 January 18 1 5 May 10 6

Now, assume today is January 25. I want to sum the numbers where the month of the date is January (i.e. B1 + B4 = 3).

I DO NOT want to create an additional column that performs the MONTH() function on the date column, and then use the month column as my SUMIF() criteria range.

I DO want to somehow perform a function on the data from my criteria range, and then use the result of that function as my criteria.

For instance, I was thinking something like this would work:
Formula:  `Please Login or Register  to view this content.`

Any help would be greatly appreciated.

Thanks!  Register To Reply

2. ## Re: SUMIF using the MONTH of the DATE in the criteria range

Welcome to the forum. There are instructions at the top of the page explaining how to attach your sample workbook.  Register To Reply

3. ## Re: SUMIF using the MONTH of the DATE in the criteria range

Use SUMPRODUCT as SUMIF will not work with MONTH and sample data did not have a July date!

=SUMPRODUCT((MONTH(A1:A5)=MONTH(TODAY()))*(B1:B5))  Register To Reply

4. ## Re: SUMIF using the MONTH of the DATE in the criteria range

Change at least one of the dates to July, then try this:

=SUMPRODUCT((MONTH(A1:A5)=MONTH(TODAY()))*B1:B5)

Excel 2016 (Windows) 32 bit
A
B
1
01-Jan
2
2
05-Mar
7
3
08-Jul
8
4
18-Jul
1
5
10-May
6
6
7
8
9
<-- Doesn't work
 Sheet: Sheet1  Register To Reply

5. ## Re: SUMIF using the MONTH of the DATE in the criteria range

Ahh, thank you!

So you can use the SUMPRODUCT() function to perform calculations on the criteria range. Basically, if your conditional statement is TRUE, then TRUE evaluates to 1. And 1 * x = x.

Likewise, FALSE evaluates to 0. And 0 * x = 0.

So then, when the sum of the products is calculated, you get the sum of those cells where your conditional evaluated to TRUE.

Very nice!  Register To Reply

6. ## Re: SUMIF using the MONTH of the DATE in the criteria range  Register To Reply

7. ## Re: SUMIF using the MONTH of the DATE in the criteria range

Thanks for the rep.   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 