Apr 10 2021 07:10 PM
Apr 11 2021 12:15 PM
SolutionThese 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)
)
Apr 11 2021 01:12 PM
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 :(
Apr 11 2021 01:35 PM
Apr 11 2021 12:15 PM
SolutionThese 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)
)