Home > Software design >  Can SUMIF in sheets find 1 criteria across multiple columns?
Can SUMIF in sheets find 1 criteria across multiple columns?

Time:06-19

I'm not sure if I've written it wrong, or if its even possible, but is there a way to SUMIF if there is a single criteria you want to across multiple columns. Such as find that criteria in column A B or C? Here is an example of my sumif statement.

=SUMIFS(Events!F:F, Events!K:K, "2 Meat", Events!L:L, "Pulled Pork", Events!M:M, "Pulled Pork",Events!N:N, "Pulled Pork", Events!D:D, "="&TODAY())

I need the bold area to be if column L says Pulled Pork OR column M says Pulled pork OR... etc. I'm hoping I've just written it incorrectly.

CodePudding user response:

try:

=SUMIFS(Events!F:F, Events!K:K, "2 Meat", 
 REGEXMATCH(Events!L:L&Events!M:M&Events!N:N, "Pulled Pork"), TRUE,       
 Events!D:D, TODAY())

CodePudding user response:

Awesome question! You can add multiple SUMIFS together in this instance to supplement an OR method, which I believe cannot be applied to SUMIFS. (copy/paste will be your friend here)

Example Formula:

=SUMIFS(Events!F:F, Events!K:K, "2 Meat", Events!L:L, "Pulled Pork", Events!D:D, "="&TODAY()) 
SUMIFS(Events!F:F, Events!K:K, "2 Meat", Events!M:M, "Pulled Pork", Events!D:D, "="&TODAY()) 
SUMIFS(Events!F:F, Events!K:K, "2 Meat", Events!N:N, "Pulled Pork", Events!D:D, "="&TODAY())

This can also be done in separate cells and then summed together for a quicker approach.

You CAN, however, apply OR conditions to filter formulas by wrapping conditions in parenthesis and including a ' ' sign in-between them, like so:

=SUM(FILTER(F:F, D:D=TODAY(), K:K="2 Meat", (L:L="Pulled Pork") (M:M="Pulled Pork") (N:N="Pulled Pork")))

Here's an example image of the results:

Image

  • Related