Problems with a SUMIFS formula

The first argument of SUMNIFS must be a single range, so B2+H4:H15 is not allowed.

Also, all ranges must be the same size. H4:H15 has 12 cells, but C2 is a single cell.

H5:H15 contains amounts, so using "Netspend Account" males no sense.

Which column contains the categories? Is that column D?

If so, try

=SUMIFS($H$4:$H$15, $D$4:$D$15, I5)+$B$2

(Not sure if the +$B$2 is needed)

Hi Hans,

Thanks for getting back to me. I like the formula, and it makes total sense of what you're saying. However, the problem here is that it checks the conditions for a single set of range arrays. However, your equation doesn't account for checking if C2=I5 and then adding B2 to the original checked array if that check is true. However, when trying to do this in various ways, I keep getting either a #VLUE or a #SPILL error in J5. Therefore, I'm wondering if I hit a limitation in Excel's formula engine in that it's not capable of checking two different range arrays in the same =SUMIFS formula. I know I could do this in VB and even SQL, but it seems that Excel can't, even when the logic and syntax make sense. Am I arriving at a false conclusion with the perceived limitation, or is there an actual way of doing this without just eliminating the spending table and just making those line items in the Ticket Item Table?

But then I got an idea based on the suspicion. If I divide the formula into a sum of two SUMIF formulas, it works. Here's what I used:

=(SUMIF(D4:D15,I5,H4:H15)+(SUMIF(C2,I5,B2)))

Testing with True and false results from both range arrays, it works perfectly and thus satisfies the intent of the functionality.

I hope this helps anyone running into a #SPILL error when trying to do a sum from two different range arrays.

Hi Erick

Let's have a video chat via TEAMS, this will be a better way to sort this out.

Let me know if you can make it.

JeovanyCV invited you to a Microsoft Teams Meeting:

Meeting with Erik Stearns, Excel Forum
Tuesday 10 June 2025
19:00 - 20:00 (GMT) = 7:00 pm LONDON time

Meeting link: Meeting with Erik Stearns Excel Forum | Microsoft Teams | Meet-up-Join

Hi Erik

Let's forget about the formula for a moment,

According to the picture you posted and the logic of your calculations, the result for the Netspend Account in cell J5 should be $11.20, meaning the sum of $0.60 + $0.60 + $10, where $10 is the "Added value" in cell B2.

So, if my assumptions are correct, then the formula given by HansV is the right one to use, and you simply did not correctly apply it.

If you copy and paste the formula in cell J5 you should get the right results

=SUMIFS($H$4:$H$15, $D$4:$D$15, I5)+$B$2

If I'm wrong, please provide more details of your calculation logic, also, add more sample values to the table, and show us the expected results.

Regards

Jeovany

Sure thing. Essentially, the purpose is to track spending on both ticket sales and top-ups through the Cap Metro app. You can either purchase individual tickets via the balance on the app or one of the account sources listed on the right. Therefore, I'm developing formulas to track which source the top-ups come from, and or the individual purchase amounts come from. The screenshot you saw before was a bit outdated and was the wrong one. The one below shows the use of the new formula listed in my latest reply. I'll show you the formulas for the amount calculations for each account source. Therefore, if I top up on the app with an amount from the Netspend account and or I make individual ticket purchases through that account, I want both the top-up amount and the purchases to be totaled. See the latest screenshot and formulas below.

Formulas used in J5-J14

=(SUMIF(D4:D15,I5,H4:H15)+(SUMIF(C2,I5,B2)))

=(SUMIF(D4:D15,I6,H4:H15)+(SUMIF(C2,I6,B2)))

=(SUMIF(D4:D15,I7,H4:H15)+(SUMIF(C2,I7,B2)))

=(SUMIF(D4:D15,I8,H4:H15)+(SUMIF(C2,I8,B2)))

=(SUMIF(D4:D15,I9,H4:H15)+(SUMIF(C2,I9,B2)))

=(SUMIF(D4:D15,I10,H4:H15)+(SUMIF(C2,I10,B2)))

=(SUMIF(D4:D15,I11,H4:H15)+(SUMIF(C2,I11,B2)))

=(SUMIF(D4:D15,I12,H4:H15)+(SUMIF(C2,I12,B2)))

=(SUMIF(D4:D15,I13,H4:H15)+(SUMIF(C2,I13,B2)))

=SUMIF(D4:D15,I14,H4:H15) - Because there is no scenario for topping up the app from a previous balance, this formula is different.

I hope this explains things further and is what you're looking for.