Need help counting if a cell = specific text in Excel 1

[dr772]

I am trying to count how many times sponge count occurs when
the Dept = OR
I tried =SUM(IF(JAN!D="or",IF(JAN!E:E="sponge count",1,0)), but only returns a 1 HELP PLEASE see table below
(D) (E)
Dept Surgery Related  
OR Sponge count
OR Sponge count
OR Positioning
OR Sponge count
OR Equipment
OR Sponge count
OR Equipment
OR Sponge count
OR Sponge count
PACU Other
OR Equipment

 

[AnotherHiggins]

=sumproduct((D21000="OR")*(E2:E1000="Sponge count"))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[byundt]

Your original formula would have worked except for the following "gotchas":
1) It needed to be array entered because you had the IF inside the SUM. Array entering means that you hold the Control and Shift keys down while pressing Enter. Excel should respond by adding curly braces { } surrounding the formula. If you don't see them, then you must select the cell, click in the formula bar, then Control Shift and Enter.

2) Array formulas (including the SUMPRODUCT by the way) may not refer to an entire column. All but one cell is OK, so you could use:
=SUM(IF(JAN!D$2$65536="or",IF(JAN!E$2:E$65536="sponge count",1,0))

You could also multiply your two Boolean expressions, then sum that (still remembering to array enter your formula):
=SUM((JAN!D$2$65536="or")*(JAN!E$2:E$65536="sponge count"))

 

[ocb]

or you can make another column at F with this formula:

IF((D2="OR");(IF((E2="Sponge count");1;0));0)

Then you got a 1 if Dept =OR AND E="Sponge count". So you can make a SUM at the bottom of F

 

[arthurbr]

Of course a pivot table will also do the trick.
Then click on the col heading above and uncheck the items you don't need ( no fancy forumlas needde, and a lot of other possibilities at hand
Cheers