Failure Rates

[Robert]

I'm stumped, I'm sure that there may be an incredibly easy solution for
someone savvy to Excel, but it's beating me up.

I have approximately 2000 records loaded into an excel spreadsheet. Here's
the breakdown of these records:

Column: Heading
A: Tractor
B: Date
C: Laser #
D: Discrepancy
E: Repair
F: Technician

What I am trying to do is determine how many times a particular laser (C)
has failed on a particular tractor (A).

Please help.

Thank you -- Robert

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A100="tractor1"),--(C2:C100="laser1"))

 

[Bob Cresto]

You could use the Auto Filter under Data. Filter for the Laser Column then
the Tractor column and you will have a list of all failures for that laser
and tractor.

 

[Robert]

=SUMPRODUCT(--(A2:A100="tractor1"),--(C2:C100="laser1"))

PERFECT!

Thank you so much. This has been killing me for days. Much appreciated.

 

[David]

Peo,
Could you please explain the use of "--" in the sumproduct
formula given. Thank you very much
David

 

[Paul]

Peo,
Could you please explain the use of "--" in the sumproduct
formula given. Thank you very much
David

SUMPRODUCT expects numeric parameters.
(A2:A100="tractor1"), for example, is boolean (true or false).
-- converts true or false to 1 or 0.
Think of - as multiplying by -1, so -- multiplies by -1 twice.

 

[Guest]

Thanks Paul