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
