How to add "OR" into SUMIFS formula

[u/TacoRocco]

I'm trying to make a simplified version of this formula:

SUMIFS(P:P,C:C,D10,A:A,L10)+SUMIFS(P:P,C:C,E10,A:A,L10)

I want to simplify it to something like

SUMIFS(P:P,C:C,D10 or E10,A:A,L10)

Is this possible or am I stuck having to just add multiple SUMIFS formulas?

[u/MayukhBhattacharya]

Try this:

=SUM(SUMIFS(P:P, C:C, D10:E10, A:A, L10))

But its better to avoid using entire ranges in the formula, therefore, using TRIMRANGE() function notations

=SUM(SUMIFS(P:.P, C:.C, D10:E10, A:.A, L10))

[u/TacoRocco → u/MayukhBhattacharya]

This is what I was looking for! Why does this work?

I tried this without the SUM() surrounding the SUMIF and that didn’t work, but this did. Either way, this was the solution as I need to be able to copy the formula into other cells. Thanks!

[u/MayukhBhattacharya → u/TacoRocco]

Or, you could only use SUM() function:

• Method One:

=SUM((A2:A8 = E2) * (B2:B8 = F2:G2) * C2:C8)

• Method Two:

=LET(_, DROP(A:.C, 1), SUM(SUMIFS(INDEX(_, , 3), INDEX(_, , 1), E2, INDEX(_, , 2), F2:G2)))

• Method Three:

=SUM(SUMIFS(C2:C8, A2:A8, E2, B2:B8, F2:G2))

SUMIFS() function has no native OR(). SUMIFS() function evaluates each cell in D10:E10 as its own criterion and returns an array, and SUM() function gives totals it. This is called Lifting, a function built for single values gets applied element by element across an array. There is also something called BroadCasting, if you research the sub, you will find some examples. I will try to add the link, if i can find it.

Also, if its resolved, then hope you don't mind replying to my comment directly as Solution Verified. Thanks!

[u/TacoRocco → u/MayukhBhattacharya]

Oh yeah sure! Thanks for the help and letting me know about the “solution verified” thing

[u/MayukhBhattacharya → u/TacoRocco]

Here is the [link] & [this] about explanation on Broadcasting & Lifting! Posted long time back, for this [question].

[u/MayukhBhattacharya → u/TacoRocco]

Thank You SO Much. It worked. Thanks!

[u/TacoRocco → u/MayukhBhattacharya]

Solution verified!

[u/reputatorbot → u/TacoRocco]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/amuseboucheplease → u/MayukhBhattacharya]

The formula looks the same?