Forum Discussion

SubhasisB's avatar
SubhasisB
Copper Contributor
Aug 22, 2020
Solved

Sumproduct with 3 conditions

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.
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 22, 2020

    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)
)
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 22, 2020

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.

mtarler's avatar
mtarler
Silver Contributor
Aug 22, 2020

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.

Resources