Forum Discussion
SPILL ERROR with SUMProduct and lookup from other sheet
These are different formulas, For example, in P7 is
=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)) )which returns number of records with July date and any of titles from Sheet1 in column H.
In T5 is different formula
=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) )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.
I'm not sure what exactly you'd like to calculate, as varioant formula could be
=SUM( (MONTH($C$16:$C$4000)=MONTH(1&O5))* COUNTIF(Sheet1!$A$1:$A$15,$H$16:$H$4000) )
These are different formulas, For example, in P7 is
=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))
)
which returns number of records with July date and any of titles from Sheet1 in column H.
In T5 is different formula
=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)
)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.
I'm not sure what exactly you'd like to calculate, as varioant formula could be
=SUM( (MONTH($C$16:$C$4000)=MONTH(1&O5))*
COUNTIF(Sheet1!$A$1:$A$15,$H$16:$H$4000)
)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.
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 😞
- 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!
