SPILL ERROR with SUMProduct and lookup from other sheet

[cparb]

• Device and OS platform, Laptop/Windows 10 
• Excel product name and version number Office 365
• Excel file Attached - specifically looking at cells T5 and T12
• 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. 

[#2 · Accepted solution]

@cparb 

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)
)

[#3]

@Sergei Baklan 

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

[#4]

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!