Discussion Re: SPILL ERROR with SUMProduct and lookup from other sheet in Excel
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2266870#M96049
<P><LI-USER uid="1022655"></LI-USER> </P>
<P>These are different formulas, For example, in P7 is</P>
<LI-CODE lang="excel">=SUMPRODUCT(--(MONTH(C16:C4000)=7),
(($H$16:$H$4000=Sheet1!$A$1)+
($H$16:$H$4000=Sheet1!$A$2)+
($H$16:$H$4000=Sheet1!$A$3)+
($H$16:$H$4000=Sheet1!$A$4)+
($H$16:$H$4000=Sheet1!$A$5)+
($H$16:$H$4000=Sheet1!$A$6)+
($H$16:$H$4000=Sheet1!$A$7)+
($H$16:$H$4000=Sheet1!$A$8)+
($H$16:$H$4000=Sheet1!$A$9)+
($H$16:$H$4000=Sheet1!$A$10)+
($H$16:$H$4000=Sheet1!$A$11)+
($H$16:$H$4000=Sheet1!$A$12)+
($H$16:$H$4000=Sheet1!$A$13)+
($H$16:$H$4000=Sheet1!$A$14)+
($H$16:$H$4000=Sheet1!$A$15))
)
</LI-CODE>
<P>which returns number of records with July date and any of titles from Sheet1 in column H.</P>
<P>In T5 is different formula</P>
<LI-CODE lang="excel">=SUMPRODUCT( --ISNUMBER(C16:C4000),--MONTH(C16:C4000)=1)*
(($H$16:$H$4000=Sheet1!$A$1)+
($H$16:$H$4000=Sheet1!$A$2)+
($H$16:$H$4000=Sheet1!$A$3)+
($H$16:$H$4000=Sheet1!$A$4)+
($H$16:$H$4000=Sheet1!$A$5)+
($H$16:$H$4000=Sheet1!$A$6)+
($H$16:$H$4000=Sheet1!$A$7)+
($H$16:$H$4000=Sheet1!$A$8)+
($H$16:$H$4000=Sheet1!$A$9)+
($H$16:$H$4000=Sheet1!$A$10)+
($H$16:$H$4000=Sheet1!$A$11)+
($H$16:$H$4000=Sheet1!$A$12)+
($H$16:$H$4000=Sheet1!$A$13)+
($H$16:$H$4000=Sheet1!$A$14)+
($H$16:$H$4000=Sheet1!$A$15)
)</LI-CODE>
<P>You sum all records with July date and multiple this numbers on array of values which indicates if any from Sheet1 is in column H. Result is array which formulas try to return. But that's not enough space for this array in the sheet, thus #SPILL error.</P>
<P>I'm not sure what exactly you'd like to calculate, as varioant formula could be</P>
<LI-CODE lang="excel">=SUM( (MONTH($C$16:$C$4000)=MONTH(1&O5))*
COUNTIF(Sheet1!$A$1:$A$15,$H$16:$H$4000)
)</LI-CODE>Sun, 11 Apr 2021 19:15:07 GMTSergei Baklan2021-04-11T19:15:07ZSPILL ERROR with SUMProduct and lookup from other sheet
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2265955#M95985
<UL><LI><STRONG>Device and OS platform</STRONG>, Laptop/Windows 10 </LI><LI><STRONG>Excel product name and version number</STRONG><SPAN> Office 365</SPAN></LI><LI><STRONG>Excel file<SPAN> </SPAN></STRONG>Attached - specifically looking at cells T5 and T12</LI><LI>I'm attempting to use the same formula that appears in cells P2:P7 - P9:P14 & T2:T4 - T6:T7 - T9:T11 - T13:T14. However, when having the formula the same it would return a value not based on the date being in January in cells C16:C4000. I attempted to add the additional section of the formula --(ISNUMBER(C16:C4000)) that was in there prior to adding the lookup of sheet1, but when I did it returned the SPILL Error. If I leave the formula without the ISNUMBER part the result will total the items in the H16:H4000 regardless of the date in C16:C4000. </LI></UL>Sun, 11 Apr 2021 02:10:37 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2265955#M95985cparb2021-04-11T02:10:37ZRe: SPILL ERROR with SUMProduct and lookup from other sheet
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2266870#M96049
<P><LI-USER uid="1022655"></LI-USER> </P>
<P>These are different formulas, For example, in P7 is</P>
<LI-CODE lang="excel">=SUMPRODUCT(--(MONTH(C16:C4000)=7),
(($H$16:$H$4000=Sheet1!$A$1)+
($H$16:$H$4000=Sheet1!$A$2)+
($H$16:$H$4000=Sheet1!$A$3)+
($H$16:$H$4000=Sheet1!$A$4)+
($H$16:$H$4000=Sheet1!$A$5)+
($H$16:$H$4000=Sheet1!$A$6)+
($H$16:$H$4000=Sheet1!$A$7)+
($H$16:$H$4000=Sheet1!$A$8)+
($H$16:$H$4000=Sheet1!$A$9)+
($H$16:$H$4000=Sheet1!$A$10)+
($H$16:$H$4000=Sheet1!$A$11)+
($H$16:$H$4000=Sheet1!$A$12)+
($H$16:$H$4000=Sheet1!$A$13)+
($H$16:$H$4000=Sheet1!$A$14)+
($H$16:$H$4000=Sheet1!$A$15))
)
</LI-CODE>
<P>which returns number of records with July date and any of titles from Sheet1 in column H.</P>
<P>In T5 is different formula</P>
<LI-CODE lang="excel">=SUMPRODUCT( --ISNUMBER(C16:C4000),--MONTH(C16:C4000)=1)*
(($H$16:$H$4000=Sheet1!$A$1)+
($H$16:$H$4000=Sheet1!$A$2)+
($H$16:$H$4000=Sheet1!$A$3)+
($H$16:$H$4000=Sheet1!$A$4)+
($H$16:$H$4000=Sheet1!$A$5)+
($H$16:$H$4000=Sheet1!$A$6)+
($H$16:$H$4000=Sheet1!$A$7)+
($H$16:$H$4000=Sheet1!$A$8)+
($H$16:$H$4000=Sheet1!$A$9)+
($H$16:$H$4000=Sheet1!$A$10)+
($H$16:$H$4000=Sheet1!$A$11)+
($H$16:$H$4000=Sheet1!$A$12)+
($H$16:$H$4000=Sheet1!$A$13)+
($H$16:$H$4000=Sheet1!$A$14)+
($H$16:$H$4000=Sheet1!$A$15)
)</LI-CODE>
<P>You sum all records with July date and multiple this numbers on array of values which indicates if any from Sheet1 is in column H. Result is array which formulas try to return. But that's not enough space for this array in the sheet, thus #SPILL error.</P>
<P>I'm not sure what exactly you'd like to calculate, as varioant formula could be</P>
<LI-CODE lang="excel">=SUM( (MONTH($C$16:$C$4000)=MONTH(1&O5))*
COUNTIF(Sheet1!$A$1:$A$15,$H$16:$H$4000)
)</LI-CODE>Sun, 11 Apr 2021 19:15:07 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2266870#M96049Sergei Baklan2021-04-11T19:15:07ZRe: SPILL ERROR with SUMProduct and lookup from other sheet
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2266905#M96051
<P><LI-USER uid="521"></LI-USER> </P><P> </P><P>The formulas in the other cells listed are accurate as they are only pulling the total from cells H16:H4000 based on the dates listed in cells C16:C4000. The only issue I'm having is the same formula is not working for January. What January is doing is that it is pulling the if the criteria is met in cells H16:H4000 regardless of the dates listed in cells C16:C4000. I attempted to make your correction provided however it is still pulling the data regardless of the dates. </P><P> </P><P>The end result for cells T5 and T12 need to be same as the other cells except it should only pull if the date in C16:C4000 is in January. This is where I'm hitting a wall with the formula <LI-EMOJI id="lia_disappointed-face" title=":disappointed_face:"></LI-EMOJI> </P>Sun, 11 Apr 2021 20:12:42 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2266905#M96051cparb2021-04-11T20:12:42ZRe: SPILL ERROR with SUMProduct and lookup from other sheet
https://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2266916#M96054
What I did just test is that if there is another date in C16:C4000 it won't pick up though so I think that it does meet my needs as there wouldn't be criteria in H16:H4000 without a date in C16:C4000 so the result won't have data that it shouldn't. Thank you!Sun, 11 Apr 2021 20:35:49 GMThttps://techcommunity.microsoft.com/t5/excel/spill-error-with-sumproduct-and-lookup-from-other-sheet/m-p/2266916#M96054cparb2021-04-11T20:35:49Z