countif, multiple value range PLEASE HELP

L

laandmc

I have a set of data where I want to count some values with a range of
different outcomes:

a b c d
a a c e

0 = COUNTIF(A2:D2,OR(A1,B1,C1,D1))
3 <-- Answer I want to get

The Bottom answer is what I want to get
The Top answe is what I am getting

Is there a way of doing this without doing a long formula such as:
= COUNTIF(A2:D2,A1)+COUNTIF(A2:D2,B1)+COUNTIF(A2:D2,C1)+COUNTIF(A2:D2,D1)

My formula is already very complicated so I want to avoid doing this if
possible
 
P

Pete_UK

This is slightly shorter:

=SUMPRODUCT((A2:D2=A1)+(A2:D2=B1)+(A2:D2=C1)+(A2:D2=D1))

Hope this helps.

Pete
 

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