SOLVED

Sumproduct with 3 conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-1606000%22%20slang%3D%22en-US%22%3ESumproduct%20with%203%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606000%22%20slang%3D%22en-US%22%3EHello%20Friends%2C%20can%20you%20please%20help%20regarding%20the%20attached%20file%2C%20where%20I%20have%203%20conditions%20for%20sumproduct%20function%2C%20I%20am%20getting%20zero%20answer%2C%20please%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606048%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20with%203%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767899%22%20target%3D%22_blank%22%3E%40SubhasisB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20dates%20are%20entered%20only%20into%20one%20cell%20from%20each%20three%20with%20Center%20Across%20Selection%20format.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20291px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214105i1CFBD778FAB74804%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20select%20Product%20A%20formula%20returns%20some%20value.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20that%20you%20need%20to%20redesign%20source%20table%20or%20construct%20more%20complex%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606049%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20with%203%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767899%22%20target%3D%22_blank%22%3E%40SubhasisB%3C%2FA%3E%26nbsp%3B%20%26nbsp%3Bhere%20is%20an%20alternative%20way%20using%20offset.%26nbsp%3B%20For%20simplicity%2C%20I%20am%20assuming%20that%20all%20of%20your%20Product%20lists%20under%20each%20date%20are%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOFFSET(Data!%24A%241%2CMATCH(C3%2CData!%24A%3A%24A)-1%2CMATCH(A3%2CData!%241%3A%241%2C0)%2BMATCH(B3%2CData!%24C%242%3A%24E%242)-1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20you%20probably%26nbsp%3B%3CSPAN%3Ecould%20or%20should%20consider%20using%20a%20pivot%20table%20to%20get%20what%20you%20want.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606051%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20with%203%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767899%22%20target%3D%22_blank%22%3E%40SubhasisB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%0A%20%20%20Data!C3%3AAO13%2C%0A%20%20%20(%20(Data!C1%3AAO1%3DAns!A2)%20%2B%0A%20%20%20%20%20(Data!D1%3AAP1%3DAns!A2)%20%2B%0A%20%20%20%20%20(Data!E1%3AAQ1%3DAns!A2)%0A%20%20%20)%20*%0A%20%20%20(Data!C2%3AAO2%3DAns!B2)*%0A%20%20%20(Data!A3%3AA13%3DAns!C2)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
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.
3 Replies

@SubhasisB 

That's since dates are entered only into one cell from each three with Center Across Selection format.

image.png

If select Product A formula returns some value.

 

With that you need to redesign source table or construct more complex formula.

@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

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