SOLVED

!SPILL error with SUMIFS formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1318250%22%20slang%3D%22en-US%22%3E!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318250%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20worksheet%20(produced%20in%20Office%20365)%20is%20part%20of%20a%20larger%20workbook%20that%20I%20am%20putting%20together%20to%20produce%20monthly%20financials.%26nbsp%3B%20There%20is%20a%20worksheet%20named%20transactions_data%20which%20is%20being%20searched%20to%20sum%20amounts%20for%20each%20vendor%20in%20column%20A%2C%20that%20is%20within%20a%20certain%20date%20range%20%24D%242%20to%20%24E%242%20(these%20are%20hidden%20in%20the%20attached%20PDF)%2C%20and%20that%20has%20text%20within%20the%20string%20of%20another%20column%20of%20transactions_data%20that%20contains%20%22expense%22%20or%20%22COGS%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20that%20I%20entered%20from%20B7%20to%20B95%3A%3C%2FP%3E%3CP%3E%3DSUMIFS(transactions_data!%24G%3A%24G%2Ctransactions_data!%24J%3A%24J%2C%24A7%2Ctransactions_data!%24B%3A%24B%2C%22%26gt%3B%3D%22%26amp%3B%24D%242%2Ctransactions_data!%24B%3A%24B%2C%22%26lt%3B%3D%22%26amp%3B%24E%242%2Ctransactions_data!%24C%3A%24C%2C%7B%22*Expense*%22%2C%22*COGS*%22%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20a%20few%20minor%20errors%20where%20the%20correct%20total%20was%20not%20picked%20up%20that%20I%20will%20have%20to%20research%2C%20but%20there%20is%20one%20odd%20error%20that%20I%20have%20never%20seen%20before%2C%20and%20even%20after%20looking%20at%20information%20on%20the%20internet%2C%20am%20unable%20to%20fix.%26nbsp%3B%20Many%20of%20the%20values%20are%20calculated%20correctly%2C%20but%20are%20%22split%22.%26nbsp%3B%20I%20assume%20that%20the%20amounts%20that%20are%20displayed%20in%20column%20B%20are%20ones%20that%20match%20%22*Expense*%22%2C%20and%20the%20ones%20in%20column%20C%20are%20the%20ones%20that%20match%20%22*COGS*%22.%26nbsp%3B%20What%20I%20can't%20figure%20out%20is%20how%20to%20make%20the%20amounts%20display%20in%20one%20column%20as%20desired%20(column%20B).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1318250%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318275%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318275%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F109467%22%20target%3D%22_blank%22%3E%40Tamara%20Duvall%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20you%20have%20an%20array%20as%20latest%20condition.%20If%20you'd%20like%20to%20have%20OR%20condition%2C%20wrap%20entire%20formula%20with%20sum%2C%20i.e.%20%3DSUM(SUMIFS(...))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318288%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318288%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%20The%20%3DSUM%20option%20worked%20beautifully.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318303%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F109467%22%20target%3D%22_blank%22%3E%40Tamara%20Duvall%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
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