# month to date calculation based on month selection from list box

1. ## month to date calculation based on month selection from list box

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

2. ## Re: month to date calculation based on month selection from list box

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)

3. ## Re: month to date calculation based on month selection from list box

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

4. ## Re: month to date calculation based on month selection from list box

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

5. ## Re: month to date calculation based on month selection from list box

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

6. ## Re: month to date calculation based on month selection from list box

See attached: Sheet3 which has Excel dates in Column C

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1