Problems with a SUMIFS formula

Hi Erik,

yes, that helps, thank you.

It is a little difficult to explain why the SPILL error is generated... I'll just skip that for now; if we apply the formulas correctly, the problem no longer exists.

Your file is well structured because the data is formatted as tables, which is not evident from your formulas. And that brings us to the first minor problem I saw in your video.

Edit a formula, for example in J5 and add +0 at the end and press ENTER. You'll see a little fx icon:

Excel checks the formulas and recognizes that you use different formulas in the table column. Your current formula calculates the correct value, that's no question.

But your formulas do not use absolute references, if you allow Excel to fill the formula down, you have a problem

J5: =(SUMIF(D4:D15,I5,H4:H15)+(SUMIF(C2,I5,B2)))+0
J6: =(SUMIF(D5:D16,I6,H5:H16)+(SUMIF(C3,I6,B3)))+0

J5 should be at min:

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

But as you have tables, you should use structured references:

=SUMIF(Table2[Source],[@[Account Name]],Table2[Amount])+SUMIF(Table1[Source],[@[Account Name]],Table1[Added])

That look complicated at the first look, but for example in H4 it is absolutely clear what is calculated:
H5: =[@Quantity]*[@Price]

The benefit of such references is that the formula in the column is the same in each row! BTW, you have a long complicated formula in G5, I replaced it with this one: =IFERROR(VLOOKUP([@[Ticket Purchases]],Table4,2,0),0)

Furthermore, tables have a Total row, therefore it's not necessary to calculate the total on your own.

For better visibility, I've added small light pink rows and columns between the tables. You can hide or delete them if you don't like them:

Here is your modified sample file:

https://www.dropbox.com/scl/fi/xk8492jqjegyg8wkdb7e0/CapMetro-Erik-Stearns-AK.xlsx?rlkey=m8fg5i259j0t65n9lzsys3jai&dl=1

Any questions?

Andreas.

I just uploaded the Excel file along with a video explaining my workaround. I hope this helps.