|
Mulitiple Count of occurrences in different columns
Count of occurrences that appear in multiple columns. For example I have 5
columns that label a particular account as a NEW or OLD, HAPPY or SAD, 1 or 2, and RED or BLACK. Each account has one of the labels and I want to count how many are labeled as OLD, HAPPY, 1, and BLACK. I can't get the "COUNTIF", "COUNT(IF..." formula's to work. Can someone assist? -- Thank you, Ant |
Mulitiple Count of occurrences in different columns
=SUMPRODUCT(N(A1:A100="OLD"),N(B1:B100="HAPPY"),N( C1:C100=1),N(D1:D100="BLACK"))
"Ant" wrote in message ... Count of occurrences that appear in multiple columns. For example I have 5 columns that label a particular account as a "NEW" or "OLD", "HAPPY" or "SAD", "1" or "2", and "RED" or "BLACK". Each account has one of the labels and I want to count how many are labeled as "OLD", "HAPPY", "1", and "BLACK". I can't get the "COUNTIF", "COUNT(IF..." formula's to work. Can someone assist? -- Thank you, Ant |
Mulitiple Count of occurrences in different columns
Hi Ant
You need a SumProduct formula. I assume one column is used for one label pair. With labels in column A:D, this formula will work: =SUMPRODUCT(--(A1:A10="OLD"),--(B1:B10="Happy"),--(C1:C10=1),--(D1:D10="black")) Hopes this helps. --- Per "Ant" skrev i meddelelsen ... Count of occurrences that appear in multiple columns. For example I have 5 columns that label a particular account as a NEW or OLD, HAPPY or SAD, 1 or 2, and RED or BLACK. Each account has one of the labels and I want to count how many are labeled as OLD, HAPPY, 1, and BLACK. I can't get the "COUNTIF", "COUNT(IF..." formula's to work. Can someone assist? -- Thank you, Ant |
Mulitiple Count of occurrences in different columns
That worked perfectly. Can you tell me what the "N" stands for in the formula?
-- Thank you, Ant "Bob Umlas" wrote: =SUMPRODUCT(N(A1:A100="OLD"),N(B1:B100="HAPPY"),N( C1:C100=1),N(D1:D100="BLACK")) "Ant" wrote in message ... Count of occurrences that appear in multiple columns. For example I have 5 columns that label a particular account as a "NEW" or "OLD", "HAPPY" or "SAD", "1" or "2", and "RED" or "BLACK". Each account has one of the labels and I want to count how many are labeled as "OLD", "HAPPY", "1", and "BLACK". I can't get the "COUNTIF", "COUNT(IF..." formula's to work. Can someone assist? -- Thank you, Ant |
| All times are GMT +1. The time now is 12:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com