Count cells in one range based on parameters in another range

[Guest]

Hi;

I want to count cells in one range on a worksheet that meet certain criteria
("Fatal", "Serious", "Minor", etc.) based on whether they are related to
parameters in another range ("Gunshot", "Stabbing", etc.)

In other words, where an occurrence has Gunshot, I need to count all
occurrences of Fatal. The count serves as input to another cell, which is
then graphed.

I've been doing this by autofiltering, but that's a manual solution, and I
would like to automate the reporting.

I've tried
=COUNTIF(ExtentofInjury_2005,"Fatal")+COUNTIF(InjuryType1_2005,"Gunshot"),
which of course counts all gunshots and all fatals(trust me, we don't have 24
fatal gunshots). And I know I could do a Pivot table; that's probably the
best solution.

I think I remember this coming up a few years back, but can't remember the
solution.

Tia for your assistance.

 

[Biff]

Hi!

Try something like this:

=SUMPRODUCT(--(A1:A100="Fatal"),--(B1:B100="Gunshot"))

Or use cells to hold the criteria:

C1 = Fatal
D1 = Gunshot

=SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))

Biff

 

[Guest]

TYVM. The SUMPRODUCT function would not would occirred to me, but I've seen
the answer before; dead brain cells.