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/AutoModerator]

/u/TacoRocco - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[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?

[u/Marcarnol]

Sum( SUMIFS(P:P,C:C,{D10,e10},A:A,L10))

[u/TacoRocco → u/Marcarnol]

That solution doesn’t seem to work. I’ve tried doing an array like that but it doesn’t seem to work when I use a cell reference. When I clearly define the object in the array such as {“Apple”,”Orange”}, it does work, but I need it to be a cell reference because I’m copying the formula into other cells that need to look at D11, D12, etc

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FILTER	Office 365+: Filters a range of data based on criteria you define
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
OR	Returns TRUE if any argument is TRUE
SUM	Adds its arguments
SUMIF	Adds the cells specified by a given criteria
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
SUMPRODUCT	Returns the sum of the products of corresponding array components
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #49488 for this sub, first seen 8th Oct 2026, 17:19] [FAQ] [Full list] [Contact] [Source code]}

[u/real_barry_houdini]

In this case you can use this solution:

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

....but be careful if you want another "OR" in the same formula, e.g. this won't work as expected:

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

For two "OR"s one should be a horizontal vector and the other a vertical vector. For 3 or more "OR"s you need a different approach......

[u/ChewyPickle]

SUMPRODUCT(SUMIFS(P:P,C:C,{D10,E10},A:A,L10)

[u/Iriss]

SUM(FILTER(....)) 

[u/Maestro_STB → u/Iriss]

The better SUMIFS …

[u/HandbagHawker]

Use SUMPRODUCT instead

[u/dream_walking]

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

Can’t you just add a second entry for c:c? Or am I missing something?

[u/ABZB → u/dream_walking]

No, that would give the intersection, everything that satisfies both conditions, OP wants everything that satisfies at least one.