Forum Discussion

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.

Sumproduct .xlsx16 KB

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

Sumproduct .xlsx25 KB

3 Replies

SergeiBaklan

Diamond Contributor

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

Sumproduct .xlsx25 KB

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