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

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

--
Don Guillett
SalesAid Software

"Anita" wrote in message
...
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

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

In article ,
Anita wrote:

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

Try SUMPRODUCT()

see:

http://www.microsoft.com/communities...0-53d4c93d69a3
--
Gary''s Student
gsnu200710


"Anita" wrote:

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

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


"Anita" wrote:

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

If you use Xl 2007 then try this:

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


"Anita" wrote:

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

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?

--
Don Guillett
SalesAid Software

"Teethless mama" wrote in message
...
If you use Xl 2007 then try this:

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


"Anita" wrote:

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