!SPILL error with SUMIFS formula

New Contributor

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:



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
Best Response confirmed by Tamara Duvall (New Contributor)

@Tamara Duvall 

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(...))


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


@Tamara Duvall , you are welcome