Count If ?

[Guest]

I have a spreadsheet with 2 columns like this:-

Incident Cause
Door Open Tech Fault
Door Open Public Error
Door 1st Catch Public Error
Door Open Tech Fault

What I want to do is count the number of (for instance) tech faults for door
open or public errors for door open or public errors for door 1st catch etc
etc. In other words count the number of causes for each incident.

Hope that makes sense and hope you can help.

Using Excel XP

Thanks

Anita

 

[Don Guillett]

try this idea
=sumproduct((a2:a22="door open")*(b2:b22="tech fault"))

 

[JE McGimpsey]

IF you only want one result:

=SUMPRODUCT(--(A2:A100="Door Open"), --(B2:B100="Tech Fault"))

(see http://www.mcgimpsey.com/excel/doubleneg.html for an explanation).

If you want multiple results, then use a Pivot Table.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Guest]

Try SUMPRODUCT()

see:

http://www.microsoft.com/communitie...&p=1&tid=9e55150a-c877-4074-8b30-53d4c93d69a3

 

[Guest]

Count for Door Open, Tech Fault:

=SUMPRODUCT((A1:A65535="Door Open")*(B1:B65535="Tech Fault"))

Count for Door 1st Catch, Public Error:

=SUMPRODUCT((A1:A65535="Door 1st Catch")*(B1:B65535="Public Error"))

.... and so on..

 

[Guest]

If you use Xl 2007 then try this:

=COUNTIFS(A:A,"Door Open",B:B,"Tech Fault")

 

[Don Guillett]

I have not installed my 2007 yet since I got the freebie install once from
the intro, but
What happens when saved to older version?