CountIf Condition based on lookup in a list of values

[Eric E]

I have a row with input data: cells with values of RED, GREEN, PINK, TAN.
I have a table/range (PRIMARY_COLORS) that includes RED, BLUE, GREEN.
I would like to count the number of valid primary colors are in the input
data row.
I cannot figure out how to include a conditional statement in the COUNTIF
function: i.e., COUNTIF the cell value is a MATCH in the PRIMARY_COLORS range.

Can this be done?

Thanks.

 

[Jacob Skaria]

Try
=COUNTIF(A:A,"red")

Or if you have A1 = red

=COUNTIF(A:A,A1)


If this post helps click Yes

 

[Jacob Skaria]

The below formula will return the number of primary colors in the array from
Row 1. Adjust the range to suit your requirement..

=SUMPRODUCT(--(ISNUMBER(MATCH(1:1,{"red","blue","green"},0))))

If you are using a named range replace the array with the named range

=SUMPRODUCT(--(ISNUMBER(MATCH(1:1,PRIMARY_COLORS,0))))

If this post helps click Yes

 

[Eric E]

Thanks. The named range formula seems to do exactly what I was looking for.
I am curious as to what the -- indicates in the formula and how it is used.

 

[Jacob Skaria]

Please find an explanasion

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[Teethless mama]

Try this:

=SUMPRODUCT(COUNTIF(data,PRIMARY_COLORS))