SOLVED

!SPILL error with SUMIFS formula

Highlighted
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:

=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

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

Highlighted

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

Highlighted

@Tamara Duvall , you are welcome