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 -