Aug 22 2020 01:43 PM
Aug 22 2020 02:20 PM
That's since dates are entered only into one cell from each three with Center Across Selection format.
If select Product A formula returns some value.
With that you need to redesign source table or construct more complex formula.
Aug 22 2020 02:26 PM
@SubhasisB here is an alternative way using offset. For simplicity, I am assuming that all of your Product lists under each date are the same.
=OFFSET(Data!$A$1,MATCH(C3,Data!$A:$A)-1,MATCH(A3,Data!$1:$1,0)+MATCH(B3,Data!$C$2:$E$2)-1)
That said, you probably could or should consider using a pivot table to get what you want.
Aug 22 2020 02:37 PM
SolutionAnother variant is
=SUMPRODUCT(
Data!C3:AO13,
( (Data!C1:AO1=Ans!A2) +
(Data!D1:AP1=Ans!A2) +
(Data!E1:AQ1=Ans!A2)
) *
(Data!C2:AO2=Ans!B2)*
(Data!A3:A13=Ans!C2)
)
Aug 22 2020 02:37 PM
SolutionAnother variant is
=SUMPRODUCT(
Data!C3:AO13,
( (Data!C1:AO1=Ans!A2) +
(Data!D1:AP1=Ans!A2) +
(Data!E1:AQ1=Ans!A2)
) *
(Data!C2:AO2=Ans!B2)*
(Data!A3:A13=Ans!C2)
)