Counting occurences with multiple entries

[Chuckles]

Hi,

I have a spreadsheet with a large volume of data. I want to count how many
times the equipment id (column A) comes up with a specific fault (Column
B)i.e Fail.

There could be numerous entires for the same piece of equipment with
different types of faults.

I've tried count, countif etc... but cant get the formula to lookup the
value and count up the occurences properly.

Can anyone help?

Thanks.

 

[Jacob Skaria]

Try

=SUMPRODUCT(--(A1:A100="equipment id"),--(B1:B100="Fail"))

With equipment id in cell C1

=SUMPRODUCT(--(A1:A100=C1),--(B1:B100="Fail"))

PS: Incase you are using XL2007 checkout help on COUNTIFS()

 

[Chuckles]

Jacob,
I'm not sure if I asked for the right help.

In one sheet I have info like below;

Equip no. Fault
1234 Light
1237 Light
1231 Light
1234 Battery
1234 Light

In another sheet I am asking the formula to check the information above and
count how many times I have a specific item.

e.g. I want to know how many times equip no 1234 had a light fault, in the
case above the answer is 2.

Many thanks for the first info.

 

[Pete_UK]

You need to include the Sheet name, like this:

=SUMPRODUCT(--(Sheet1!A1:A100=1234),--(Sheet1!B1:B100="Light"))

Adjust the ranges to suit your data.

Hope this helps.

Pete

 

[Francis]

Hi

Will this do what you want, I am using the cell C1 and D1 to hold the value
therefore, eg. you need to type 1234 in C1 and Light in D1
Place this formula in C2

=SUMPRODUCT(--(A2:A7=C1),--(B2:B7=D1))

where C1 is the Equip no and D1 is the Fault
--
Hope this help

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

 

[Ashish Mathur]

Hi,

Create a pivot table. Drag equip no. to the row area, Fault to the column
area and fault (again) to the data area

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com