Finding Value in a Cell In Another Sheet
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
Comments

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?

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))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!