# =SUMPRODUCT Question for the PROS

#### hoytforlife

##### New Member
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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)))

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

geniusssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss

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

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

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

Excel Workbook
ABC
5Criteria
6Alarm01/01/122
7
6a
Excel 2003
Cell Formulas
RangeFormula
C6=SUMPRODUCT(--(Main!A2:A1000=A6),--(Main!B2:B1000-DAY(Main!B2:B1000)+1=B6))

One more alternative

Replies
3
Views
222
Replies
13
Views
726
Replies
3
Views
730
Replies
1
Views
321
Replies
1
Views
221

1,203,396
Messages
6,055,161
Members
444,766
Latest member
bryandaniel5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back