Sumifs match ANY from one column
So I have two sheets. Neither need to be pretty. One is the basic entry sheet where data should be pulled from and looks a bit like this.
There's colours in column A, random fruit in column B and the value of what those two together would be in any given situation in Column C. That's all entirely manual and based on whatever I need when I'm inputting. The idea behind it is that nothing is entirely unique. You can see Apples can be on the same row as Red or Green, similarly nearly everything on this list is next to the word Red.
The trouble I run into is on the calculating sheet.
Column A is now made up of SOME colours from the Entry Sheet. This is a dynamic list that can change depending on other inputs so the number of rows won't always be the same.
Column B successfully uses UNIQUE, FILTER, and IFERROR to search Column B on the Entry Sheet, and return all the different values where the value in the A column on the Entry sheet appears SOMEWHERE in the A column on the Calculate sheet. I can go ahead and add a "Green Frog" to my entry sheet but he won't show up here. For those curious the formula here is:
=unique(FILTER(Entry!B:B,iferror(match(Entry!A:A,A:A,0))))
So far so swell.
Now I want to add them. I've ended up, because many hours on google took me there, using some kind of SUMIFS but it's producing the result pictured. The actual formula in C1 is
=SUMIFS(Entry!C:C,Entry!A:A,A:A,Entry!B:B,B1)
The result in C1 is exactly what I want. 5 is indeed the number of Red Apples and does not include the number of Green apples.
However, the same formula doesn't produce the desired result for the rest of the column. All other returns are '0' because the word 'Red' in the A column is only on the top row and obviously 'Yellow' is also not on the same row as 'Grape'.
So the question is, how to I get the 'Entry!A:A,A:A' to essentially make that particular criteria say "See these? Yes ALL of these please"
try:
=SUM(FILTER(C:C; B:B=F1; REGEXMATCH(A:A; TEXTJOIN("|"; 1; E:E))))
