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 my trouble is there are other items in the sponge count colum I tried =sumproduct((d2:d1000="or")*(e2:e1000="*sponge count*")) but it won't pull it out
see below
(D) (E)
Dept Surgery Related
OR Sponge count/needlecount/abrasion
OR Sponge count/abrasion
OR Positioning/or count
PACU Other/spoonge count
LITHO Equipment

 

[firefytr]

You can't use a wildcard like that with SUMPRODUCT. But you can use something else ...

=SUMPRODUCT((D21000="OR")*(ISNUMBER(SEARCH("sponge count",E2:E1000,1))))

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[xlhelp]

In another column (F for example) create a range by using =find("Sponge",E6,1)

Then, use
=SUMPRODUCT((D61000="or")*(ISNUMBER(F6:F1000)))

Me transmitte sursum, Caledoni!

 

[dr772]

Zack,
tried that got #NA..any other ideas

 

[firefytr]

Worked for me. Try using the Formula Auditor to see what results your getting.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[dr772]

Zack,
Sorry...tried again it worked..
i can't explain how great that is
- Diane

 

[firefytr]

Glad you got it working.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci