month to date calculation based on month selection from list box

[jamaludheen]

Hello,

Could you please support to get the formula for Month to date calculation based on month selection from the list box. Currently I have made with SUMIFS formula and it is getting too long due adding each month with if condition. Here is the current formula. is there any way to reduce or replace it this. the sample file has multiple columns selection also.

IF(K4="jan",SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,$K$4),IF($K$4="feb",SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,"jan")+SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,$K$4),IF($K$4="mar",SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,"jan")+SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,"feb")+SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,$K$4),0))).

The sample file is attached.

regards,
Jamaludheen

[etaf]

you are already using a sumifs
try on its own
=SUMIFS(H:H,A:A,K3,C:C,K4)

Put in K8 And copy to the other cells
=SUMIFS($H:$H,$B:$B,K$7,$D:$D,$J8,$A:$A,$K$3,$C:$C,$K$4)


You also have the dates entered as Text, it does not matter in this case , but may do in future manipulation

Not sure why you have the IF test
and then SUMIFS using the Month criteria again

you can modify the SUMIFS() to include the 2015 and 2016 cells and the channel

=SUMIFS($H:$H,$B:$B,K$7,$D:$D,$J8,$A:$A,$K$3,$C:$C,$K$4)

[jamaludheen]

Hi,

Thanks for your reply. Your formula is working for the month individual month. But I need to cumulative total. E.g. If I select FEB, then it should calculate JAN+FEB, if MAR, then JAN+FEB+MAR so on...

[etaf]

Oh Ok,
in which case you do need to change the month to be a "real" month , it can still display just the 3 letters
Jan, Feb. March etc

so when you have Sep - do you want Jan-Sept ?

you can then continue to use the SUMIFS()
but you can say >= date , <= date

so >= 1st Jan and <= 31st Mar

but the data will need to be reformated

i have to leave now , so hopefully others will reply, otherwise I may get a chance to review this evening UK time

[JohnTopley]

In K8

=SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=$K$3)*($B$2:$B$1000=K$7)*($D$2:$D$1000=$J8)*(MONTH($C$2:$C$1000 &0)<=MONTH($K$4&0)))


Copy across and down

Results in table N:O

[JohnTopley]

See attached: Sheet3 which has Excel dates in Column C