Suppose we have the following table in Google Sheets:
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | a | green apple | apple | =SUMIFS(A:A, B:B,"*" & D1 & "*", C:C,"*"&D1&"*") |
| 1 | Orange | banana | =SUMIFS(A:A, B:B,"*" & D2 & "*", C:C, "*" & D2 & "*") | |
| 20 | a | red apple | ||
| 1 | banana | |||
| 1 | kiwi | |||
| 1 | Banana |
Then E1 == 0 and E2 == 2. This is because SUMIFS sums the values of B column if ALL the criteria for all ranges are TRUE, this is equivalent to say that SUMIFS joins all criteria with an AND (logical) operator. What I need is the same SUMIFS operation but with an OR operator so that E1 == 21.
One solution is to concatenate B and C values in F column and then simply use this formula
=SUMIF(F:F, "\*" & D1 & "\*", B:B)
Is there another way to do this without having to create another column?
CodePudding user response：
If your real application only needs to find any items from D:D in either of only two columns and then return one final total, you can use this:
=ArrayFormula(SUM(FILTER(A:A,NOT(ISERROR(REGEXEXTRACT(LOWER(B:B&"~"&C:C),JOIN("|",FILTER(LOWER(D:D),D:D<>""))))))))
If your real application needs to find any items from D:D in more that two columns and then return one final total, you can either continue to join columns like this — B:B&"~"&C:C — or use a formula like this:
=ArrayFormula(SUM(FILTER(A:A,NOT(ISERROR(REGEXEXTRACT(LOWER(TRANSPOSE(QUERY(TRANSPOSE(B:C),,COLUMNS(B:C)))),JOIN("|",FILTER(LOWER(D:D),D:D<>""))))))))
If you need a per-item count of the strings in D:D if they appear any number of times in other columns, try this:
=ArrayFormula(QUERY(TRIM(QUERY(SPLIT(FLATTEN(IF(NOT(ISNUMBER(SEARCH(TRANSPOSE(FILTER(D:D,D:D<>"")),LOWER(TRANSPOSE(QUERY(TRANSPOSE(B:C),,COLUMNS(B:C))))))),,TRANSPOSE(FILTER(LOWER(D:D),D:D<>""))&"~"&A:A)),"~"),"Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 LABEL SUM(Col2) ''")),"WHERE Col2 Is Not Null"))
CodePudding user response：
If there will only ever be 2 columns to consider, then it makes sense to go with JohnSUN's suggestion from the comments.
Otherwise:
=SUMPRODUCT(N(MMULT(N(ISNUMBER(SEARCH(D1,B$1:C$6))),TRANSPOSE(COLUMN(B$1:C$6)^0))>0),A$1:A$6)
The range referenced (B$1:C$6) can be extended to one comprising as many columns as desired, though bear in mind that, having switched from a SUMIF set-up to a SUMPRODUCT one, you would be strongly advised to not use entire column references (at least in Excel this is the case; not sure about Sheets).