=SUMPRODUCT Question for the PROS

[hoytforlife]

Gents,

I am so NEW to excel and I know what I want to do but do not know how to make it happen and I need your help.

On sheet 2 I want to be able to reference sheet 1 called (MAIN) and search in Column A (INCIDENTS) for specific text then I want to match the text string to Column B (DATE) and report back the number of times that incident happened for that specific month...

Example

ALARM 1 JAN 2012
REPORT 1 JAN 2012
ALARM 15 JAN 2012
SPEED 4 MAR 2012

So essentially I am looking for excel to see how many times ALARM occurred in the month of JAN. For the above example I would like the response of 2 to be correct.

Please help I do not know how to conduct =SUMPRODUCT formula for this to happen....

 

[hoytforlife]

Here is what I have so far but it only looks for 1-Jan-2012. I need it to look from 1 Jan 2012-31 Jan 2012...

=SUMPRODUCT((MAIN!A2:A1000="ALARM")*(MAIN!B2:B1000=DATE(2012,1,1)))

 

[MARK858]

One way might be

=SUMPRODUCT(( YEAR(MAIN!B2:B1000)=2012)*( MONTH(MAIN!B2:B1000)=1)*( MAIN!A2:A1000="ALARM"))

 

[hoytforlife]

geniusssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss

 

[MARK858]

geniusssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss

Thanks for the feedback but I definitely do not fall into that category(or that's what the missus just told me anyway)

 

[Rick Rothstein]

Here is another way to do it that should also work...

=SUMPRODUCT(1*(A1:A4&TEXT(B1:B4,"\/m/yy")="ALARM/1/12"))

 

[Dave Patton]

Excel Workbook
	A	B	C
5	Criteria
6	Alarm	01/01/12	2
7
6a

Excel 2003

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(--(Main!A2:A1000=A6),--(Main!B2:B1000-DAY(Main!B2:B1000)+1=B6))

One more alternative