Mulitiple Count of occurrences in different columns

A

Ant

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?
 
B

Bob Umlas

=SUMPRODUCT(N(A1:A100="OLD"),N(B1:B100="HAPPY"),N(C1:C100=1),N(D1:D100="BLACK"))
 
P

Per Jessen

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

Ant

That worked perfectly. Can you tell me what the "N" stands for in the formula?
--
Thank you,
Ant


Bob Umlas said:
=SUMPRODUCT(N(A1:A100="OLD"),N(B1:B100="HAPPY"),N(C1:C100=1),N(D1:D100="BLACK"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top