PDA

View Full Version : [SOLVED] Sum on the basis of multiple creterias



Veeru
03-22-2018, 10:39 PM
Hi,

Looking for forumla which will sum mynumbers on the basis of mutiple creteris mentioned in sheet 1 and then sum of YTD n sheet .

Tryng to use sumproduct,can anyoe suggest anything please

Thank you

p45cal
03-23-2018, 04:37 AM
=SUMPRODUCT(((A2:A23=A29)+(A2:A23=A30)+(A2:A23=A31))*(B2:B23=B29)*(C2:C23=C 29)*D2:O23)

S.White89
03-23-2018, 07:44 AM
could you try the =SUMIFS formula?

Veeru
03-25-2018, 05:31 AM
Thanks p45cal...and what about if we only want YTD sum only.....@S.White Sumifs whould not work

p45cal
03-25-2018, 06:20 AM
=SUMPRODUCT(((A2:A23=A29)+(A2:A23=A30)+(A2:A23=A31))*(B2:B23=B29)*(C2:C23=C 29)*OFFSET(D2:O23,0,0,,MATCH(F30,$D$1:$O$1,0)))or

=SUMPRODUCT(((A2:A23=A29)+(A2:A23=A30)+(A2:A23=A31))*(B2:B23=B29)*(C2:C23=C 29)*OFFSET(D2:O23,0,0,,MATCH(TEXT(TODAY(),"mmm"),$D$1:$O$1,0)))or
=SUMPRODUCT(((A2:A23=A29)+(A2:A23=A30)+(A2:A23=A31))*(B2:B23=B29)*(C2:C23=C 29)*$D$2:$D$23:INDEX($D$2:$O$23,0,MATCH(F30,$D$1:$O$1,0)))or
=SUMPRODUCT(((A2:A23=A29)+(A2:A23=A30)+(A2:A23=A31))*(B2:B23=B29)*(C2:C23=C 29)*$D$2:$D$23:INDEX($D$2:$O$23,0,MATCH(TEXT(TODAY(),"mmm"),$D$1:$O$1,0)))