So I have a spreadsheet in Calc that has a master list in column A followed by a random list in column B, going something like this:
| A | B
1 | RED | RED
2 | YELLOW | YELLOW
3 | GREEN | YELLOW
4 | BLUE | GREEN
5 | | BLUE
6 | | RED
7 | | YELLOW
8 | | WHITE
9 | | RED
10 | | GREEN
11 | | BLUE
12 | | BLACK
13 | | BROWN
14 | | GREEN
15 | | BLUE
16 | | RED
17 | | YELLOW
18 | | GREEN
19 | | BLUE
And I know how to use COUNTIF to count how many times each color from A occurs in B. For example, I’d use COUNTIF(B:B,A1) in cell C1 for the number of times RED shows up in B, COUNTIF(B:B,A2) in C2 for YELLOW, etc. But there are also values that are in B but not in A (e.g. BLACK, WHITE, BROWN), and I want to lump the count of those together into a single count of “Other” colors since they don’t appear as often. How would I go about doing that with an auto-adapting formula?
I’ve tried using COUNTA(B:B) - SUM($C1:$C4), but I have to manually change that when increasing the size of A–say, to add BLACK and WHITE but let BROWN remain unlisted. For that same reason, using COUNTIFS(B:B, “<>” & A1, B:B, “<>” & A2,…) was not feasible for me (especially if A were to end up massive). I also tried working with SUMPRODUCT and arraying ISERROR(MATCH()), but both give me “0” instead of the right count. Finally, COUNTIF(B:B,"<>" & A:A) just gives me the length of list B, which I definitely don’t want.
Essentially, what I want to do is almost the same as this, but instead of adding arbitrary sums I just want to count values not found in a master list.
Thanks in advance for the help!