SUMIF month criteria, how?

This is a discussion on SUMIF month criteria, how? within the Excel Questions forums, part of the Question Forums category; Hi, using SUMIF, the comparison column contains dates in mm/dd/yyyy format. I want to use the month from that column ...

1. SUMIF month criteria, how?

Hi,
using SUMIF, the comparison column contains dates in mm/dd/yyyy format.
I want to use the month from that column as the SUMIF criteria.

I tinkered with the MONTH() function to no avail.

Any ideas how to do it?

Thanks!

2. Re: SUMIF month criteria, how?

Try
Code:
`=SUMPRODUCT(IF(MONTH(A1:A10)=1,B1:B10))`
Enter code with Ctrl-Shift-Enter.

A1:A10 = dates
B1:B10 = summable numbers
1 = month

3. Re: SUMIF month criteria, how?

Originally Posted by dafan
=SUMPRODUCT(IF(MONTH(A1:A10)=1,B1:B10))
You can dispense with the IF, then you don't need CSE, i.e.

=SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10)

Note: Empty cells are deemed to be a January date so you might also want to check that A1:A10 is a date, i.e.

=SUMPRODUCT(ISNUMBER(A1:A10)*(MONTH(A1:A10)=1),B1:B10)

=SUMPRODUCT((YEAR(A1:A10)=2008)*(MONTH(A1:A10)=1),B1:B10)

4. Re: SUMIF month criteria, how?

Thanks dafan.
What do you mean by enter code with ctrl-shift-enter? Don't I just enter this as a formula?

5. Re: SUMIF month criteria, how?

With array formulas you need to Ctrl-Shift-Enter. check out the article on CSE-formulas here: http://www.mrexcel.com/tip011.shtml

Barry: What does -- do here? Im not really familiar with SUMPRODUCT but I thought this would be a good situation to use it.

6. Re: SUMIF month criteria, how?

Some options...

[1] Build a pivot table, grouping dates by months.

Lets the comparison column be A and the column to sum B (Why not give them self right away?)...

[2]

=SUMIF(A:A,">="&E2,B:B)-SUMIF(A:A,">="&F2,B:B)

where E2 is the month of interest specified as: 2008-01-01 and F2 as 2008-02-01.

[3]

Control+shift+enter, not just enter...
Code:
```=SUM(
IF(ISNUMBER(\$A\$2:\$A\$500),
IF(\$A\$2:\$A\$500-DAY(\$A\$2:\$A\$500)+1=E2,
\$B\$2:\$B\$500)))```
Originally Posted by dwest100
Hi,
using SUMIF, the comparison column contains dates in mm/dd/yyyy format.
I want to use the month from that column as the SUMIF criteria.

I tinkered with the MONTH() function to no avail.

Any ideas how to do it?

Thanks!

7. Re: SUMIF month criteria, how?

Thanks to all for the education!

Posting Permissions

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