CountIf Condition based on lookup in a list of values
 

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.

Answer: CountIf Condition based on lookup in a list of values
 

Yes, this can be done using the COUNTIF function with a condition based on a lookup in a list of values. Here are the steps to follow:

1. Select a cell where you want to display the result of the COUNTIF function.
2. Type the following formula in the formula bar:
   
   
   Code:

   ---

   =COUNTIF(PRIMARY_COLORS,A1)+COUNTIF(PRIMARY_COLORS,B1)+COUNTIF(PRIMARY_COLORS,C1)+COUNTIF(PRIMARY_COLORS,D1)

   ---

   Note: A1, B1, C1, and D1 are the cells that contain the input data.
   
3. Press Enter to apply the formula.

The formula will count the number of times the values in the input data row match any of the values in the PRIMARY_COLORS range.

Alternatively, you can use the SUMPRODUCT function to achieve the same result with a single formula. Here's how:

1. Select a cell where you want to display the result of the formula.
2. Type the following formula in the formula bar:
   
   
   Code:

   ---

   =SUMPRODUCT(COUNTIF(PRIMARY_COLORS,A1:D1))

   ---

   Note: A1:D1 are the cells that contain the input data.
   
3. Press Enter to apply the formula.

The formula will count the number of times the values in the input data row match any of the values in the PRIMARY_COLORS range.

CountIf Condition based on lookup in a list of values
 

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

Or if you have A1 = red

=COUNTIF(A:A,A1)


If this post helps click Yes
---------------
Jacob Skaria


"Eric E" wrote:

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.

CountIf Condition based on lookup in a list of values
 

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
---------------
Jacob Skaria


"Eric E" wrote:

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.

CountIf Condition based on lookup in a list of values
 

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" wrote:

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
---------------
Jacob Skaria


"Eric E" wrote:

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.

CountIf Condition based on lookup in a list of values
 

Please find an explanasion

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
--
If this post helps click Yes
---------------
Jacob Skaria


"Eric E" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Eric E" wrote:

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.

CountIf Condition based on lookup in a list of values
 

Try this:

=SUMPRODUCT(COUNTIF(data,PRIMARY_COLORS))


"Eric E" wrote:

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.