Count cells in one range based on parameters in another range
 

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(Inju ryType1_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.

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

-----Original Message-----
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.
.

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

"Biff" wrote:

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

-----Original Message-----
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.
.