SOLVED

Highlighted
New Contributor

# !SPILL error with SUMIFS formula

This worksheet (produced in Office 365) is part of a larger workbook that I am putting together to produce monthly financials.  There is a worksheet named transactions_data which is being searched to sum amounts for each vendor in column A, that is within a certain date range \$D\$2 to \$E\$2 (these are hidden in the attached PDF), and that has text within the string of another column of transactions_data that contains "expense" or "COGS".

This is the formula that I entered from B7 to B95:

=SUMIFS(transactions_data!\$G:\$G,transactions_data!\$J:\$J,\$A7,transactions_data!\$B:\$B,">="&\$D\$2,transactions_data!\$B:\$B,"<="&\$E\$2,transactions_data!\$C:\$C,{"*Expense*","*COGS*"})

There are a few minor errors where the correct total was not picked up that I will have to research, but there is one odd error that I have never seen before, and even after looking at information on the internet, am unable to fix.  Many of the values are calculated correctly, but are "split".  I assume that the amounts that are displayed in column B are ones that match "*Expense*", and the ones in column C are the ones that match "*COGS*".  What I can't figure out is how to make the amounts display in one column as desired (column B).

Any help would be appreciated.

3 Replies
Highlighted
Best Response confirmed by Tamara Duvall (New Contributor)
Solution

# Re: !SPILL error with SUMIFS formula

That's since you have an array as latest condition. If you'd like to have OR condition, wrap entire formula with sum, i.e. =SUM(SUMIFS(...))

Highlighted

# Re: !SPILL error with SUMIFS formula

Thank you so much! The =SUM option worked beautifully.

Highlighted

# Re: !SPILL error with SUMIFS formula

@Tamara Duvall , you are welcome