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.

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()


--
Jacob


"Chuckles" wrote:

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







"Chuckles" wrote:

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.

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

On Dec 7, 5:25*pm, Chuckles
wrote:
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.



"Chuckles" wrote:
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.- Hide quoted text -

- Show quoted text -

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





"Chuckles" wrote:

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.







"Chuckles" wrote:

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.

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

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







"Chuckles" wrote:

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.