Sumproduct with 3 conditions

[SubhasisB]

Hello Friends, can you please help regarding the attached file, where I have 3 conditions for sumproduct function, I am getting zero answer, please help.

[#2]

@SubhasisB 

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.

[#3]

@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.

[#4 · Accepted solution]

@SubhasisB 

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