Finding Value in a Cell In Another Sheet

Art Schneiderheinze

In Sheet 1, I want to COUNT instances of value across a range of cells in Sheet 2, with some cells containing multiple values, separated by commas.

Let's say Sheet 2, has the following values in a range of rows:

SHEET 2

1: |   blue               |   Group 1

2: |   blue, red        |   Group 2

3: |   green             |   Group 1

4: |   red, yellow     |   Group 1

5: |   red                 |   Group 2

Then in Sheet 1, I want to reference that range (i.e., the column as a whole), and count how many rows contain each color.

SHEET 1

• How many BLUE? --> 2
• How many RED? --> 3
• How many YELLOW? --> 1
• How many GREEN? --> 1

I am having trouble writing the formula that counts the number of instances of the color. Before, I only had one color in each row in Sheet 2, so it was easy to count:

=COUNTIF({ColorColumnSheet2}, Color@row)

This counted correctly. However, now I want to have more than one color in some rows, and I can't get it to count correctly. It would count only 1 for blue since only row 1 in Sheet 2 contains just "blue". It skips row 2, which contains but does not equal blue.

I thought I could use FIND somehow, but I get #INVALID errors:

=COUNTIF(FIND(Color@row, {ColorColumnSheet2}) > 1)

I know this won't work cause {ColorColumnSheet2} is a range of rows. How can I get it to look at each row in the range, and then count the row if the row contains Color@row?

Then, how can I count the number of colors based on the GROUP (the second column in Sheet 2? For example,

SHEET 1

• How many BLUE in GROUP 1? --> 1
• How many BLUE in GROUP 2? --> 1
• How many RED in GROUP 1? --> 1
• How many RED in GROUP 2? --> 2
• How many YELLOW in GROUP 1? --> 1
• How many YELLOW in GROUP 2? --> 0
• How many GREEN in GROUP 1? --> 1
• How many GREEN in GROUP 2? --> 0

Art Schneiderheinze

I am able to determine if any of the rows in that range contain the value (i.e., do any of the rows contain "blue" when the group number is 1?).

=IF(AND(CONTAINS(Color@row, {ColorColumnSheet2}), CONTAINS("1", {GroupColumnSheet2})), "Yes", "")

However, how do I count how many rows in Sheet 1 contain "blue" and are in group 1, not just do any of the rows contain "blue" and are in group 1?

Paul Newcome

You are correct that you can use the FIND function. It's just a syntax issue. You would still define your range first, then use the FIND > 0 as your criteria with the range inside of the find function being an "@cell" reference.

 

=COUNTIFS({ColorColumnSheet2}, FIND("blue", @cell) > 0)

 

Keep in mind that FIND is case specific, so if you enter "blue" in as the text to search for, it will not count "Blue". To continue using the FIND function, I would suggest converting the case of the @cell to either UPPER or LOWER

 

=COUNTIFS({ColorColumnSheet2}, FIND("blue", LOWER(@cell)) > 0)

.

CONTAINS however is not case sensitive. To utilize this, the value returned by the CONTAINS function is either true or false. If you do not specify a true or false using an equals sign, it will count if true.

 

=COUNTIFS({ColorColumnSheet2}, CONTAINS("blue", @cell))