Sum on the multiple criteria

[Veeru106]

HI,


I am looking for one formula which gives me sum of on the basis of creteria as we change month.


In attached sheet we need sum of Jan month for Tyy name,1000 Id ,for Fin-HR-Mkt and same goes for Feb month


My current month is Feb and previous month Jan…so when I change add march month data and change my current month to March and previous month to feb ….formulashould pick it up.


SUmproduct is working but data will not update if my month changes


Can you please look at it.


Thanks

 

[Hui]

J22: =SUMPRODUCT(($G$7:$G$18=$G$22)*($H$7:$H$18=$H$22)*($I$7:$I$18=$I$22)*($K$7:$K$18=J21)*($L$7:$L$18))

 

[Hui]

If you want all the criteria in Column I use:
J22: =SUMPRODUCT(($G$7:$G$18=$G$22)*($H$7:$H$18=$H$22)*(($I$7:$I$18=$I$22)+($I$7:$I$18=$I$23)+($I$7:$I$18=$I$24))*($K$7:$K$18=J21)*($L$7:$L$18))

 

[Veeru106]

Thanks Hui.. there is a miss here.



It is working dine when I have Jan month in J21 but when my month changes in J21 (which is linked to C6) and march’18 will get added into the file .


Then my cell J21 will be Feb and K21 will be Mar’18.


Please assist

 

[Hui]

You need to copy the formula in J22 across to K22

 

[Veeru106]

Yes but that will solve purpose only if we want march month next to Feb'18 in cell L22 but that is not the case i will change my CM and PM in cell B6 n C6 and then my J21 n K21 will change to Feb and March respectively.

 

[Veeru106]

if we need to add if condition to make it working....so forumula should be
=IF($C$6=$J$21,SUMPRODUCT(($G$7:$G$18=$G$22)*($H$7:$H$18=$H$22)*(($I$7:$I$18=$I$22)+($I$7:$I$18=$I$23)+($I$7:$I$18=$I$24))*($K$7:$K$18=J21)*($L$7:$L$18)),0)

 

[Veeru106]

Thanks for looking at it....Appreciate your time..

 

[Hui]

But C6 is always equal to J21 as J21 contains =C6, so there is no need for that check

You control J21 and K21 by changing C6 and B6 respectively