Counting occurences with multiple entries
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. |
Counting occurences with multiple entries
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. |
Counting occurences with multiple entries
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. |
Counting occurences with multiple entries
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 - |
Counting occurences with multiple entries
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. |
Counting occurences with multiple entries
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. |
All times are GMT +1. The time now is 06:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com