Discussion Re: !SPILL error with SUMIFS formula in Excel
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318288#M59228
<P>Thank you so much! The =SUM option worked beautifully.</P>Sat, 18 Apr 2020 16:15:31 GMTTamara Duvall2020-04-18T16:15:31Z!SPILL error with SUMIFS formula
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318250#M59225
<P>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". </P><P> </P><P>This is the formula that I entered from B7 to B95:</P><P>=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*"})</P><P> </P><P>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).</P><P> </P><P>Any help would be appreciated.</P>Sat, 18 Apr 2020 15:46:57 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318250#M59225Tamara Duvall2020-04-18T15:46:57ZRe: !SPILL error with SUMIFS formula
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318275#M59227
<P><LI-USER uid="109467"></LI-USER> </P>
<P>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(...))</P>Sat, 18 Apr 2020 16:07:28 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318275#M59227Sergei Baklan2020-04-18T16:07:28ZRe: !SPILL error with SUMIFS formula
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318288#M59228
<P>Thank you so much! The =SUM option worked beautifully.</P>Sat, 18 Apr 2020 16:15:31 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318288#M59228Tamara Duvall2020-04-18T16:15:31ZRe: !SPILL error with SUMIFS formula
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318303#M59231
<P><LI-USER uid="109467"></LI-USER> , you are welcome</P>Sat, 18 Apr 2020 16:26:05 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumifs-formula/m-p/1318303#M59231Sergei Baklan2020-04-18T16:26:05Z