Solved: Sumproduct for Months

[Hoopsah]

Hi

just wondering if anyone knows a nicer formula for retrieving the month from a cell.

I am trying to do a sumproduct where there are 3 columns,

Column 1 - Date (01/01/99)
Column 2 - Region (Ness, Don, Clyde...)
Column 3 - Complaint (Adoption, Capacity...)

the problem I have is that the date is stored as 01/01/99 and I want to run the formula to extract only the ones that fall under a specific month.

I created a column with the formula MONTH() but is there a way I can incorporate it into the SUMPRODUCT formula?

Thanks for any help

[Bob Phillips]

Try using

=SUMPRODUCT(--(A2:A200<>"",--(MONTH(A2:A200)=1),...)

[Hoopsah]

Hi Bob,

sorry about this, I know it is my compiling that is in error here, but I have put in the following formula and can't get Excel to accept it:

SUMPRODUCT(--(A2:A200<>"",--(MONTH(A2:A200)=1),--(Sheet1!E2:E200=D3),--(Sheet1!J2:J200=E3)))

[Bob Phillips]

Sorry, that was my bad, I missed a bracket

=SUMPRODUCT(--(A2:A200<>""),--(MONTH(A2:A200)=1),--(Sheet1!E2:E200=D3),--(Sheet1!J2:J200=E3))

Shouldn't A2:A200 be preceded by Sheet1!?

[Hoopsah]

Magic!!

Originally It wasn't going to be Sheet 1 for A2:A200 but now that I see this working it is easier to do it like that.

Thanks again Bob, works perfectly

Cheers

Gerry