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!