SOLVED

# Sumproduct with 3 conditions

Occasional Contributor

3 Replies

# Re: Sumproduct with 3 conditions

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.

# Re: Sumproduct with 3 conditions

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

best response confirmed by SubhasisB (Occasional Contributor)
Solution

# Re: Sumproduct with 3 conditions

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