Multiple Criteria, Lookup & Count formula.

[JapanDave]

Hey everyone, I am stuck again.

I am making an accident report where I need to use a Lookup and count formula (I think).
I have two Criteria to lookup and count when a certain type of accident happens in a certain location. Obviously the location and accident type is dynamic, so I am looking for a formula that will be able to count with two lookup criteria.

I have a more detailed explanation in the excel file which should makes things eaiser to understand.

[ConneXionLost]

Enter this:

Please Login or Register  to view this content.

in cell C4, and autofill down & across.

Cheers,

[Paul]

Hi Dave,

In C4 use the following formula (works in Excel 2007+):

=COUNTIFS($G$11:$G$20,C$3,$C$11:$C$20,$B4)

Fill down and across. If you need to be backward compatible, try SUMPRODUCT, e.g.:

=SUMPRODUCT(--($G$11:$G$20=C$3),--($C$11:$C$20=$B4))

[JapanDave]

Thanks Guys, it worked like a charm.