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

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.

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

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

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!

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

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

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

Thanks for the rep.

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1