SUB Total dynamic range based on cell value

[Melimob]

Hi

I have a table which has a total row.

the headers of the table refer to dates.

At the moment I have a formula sub totaling the whole from to date range however I want the user to be able to use a drop down where they can choose the start date range and for the total to update accordingly.

I've added in a dropdown with the dates.

Here's my original formula:
=SUM(Table310[[#Totals],[11-Mar]:[19-Aug]])

the drop down is in A11
so I want to say for example, whatever value is in A11 replaces the 11-MAR in formula.
E.g. A11 > 01-Apr

formula would update to: =SUM(Table310[[#Totals],[01-Apr]:[19-Aug]])

I was going to do a vlkup on the date to update the formula but as I have this across many different tables I was hoping there was a way to have the formula reference A11.

e.g. =SUM(Table310[[#Totals],value of A11:[19-Aug]])

Many thanks for any advice.

 

[Melimob]

Hi

I have a table which has a total row.

the headers of the table refer to dates.

At the moment I have a formula sub totaling the whole from to date range however I want the user to be able to use a drop down where they can choose the start date range and for the total to update accordingly.

I've added in a dropdown with the dates.

Here's my original formula:
=SUM(Table310[[#Totals],[11-Mar]:[19-Aug]])

the drop down is in A11
so I want to say for example, whatever value is in A11 replaces the 11-MAR in formula.
E.g. A11 > 01-Apr

formula would update to: =SUM(Table310[[#Totals],[01-Apr]:[19-Aug]])

I was going to do a vlkup on the date to update the formula but as I have this across many different tables I was hoping there was a way to have the formula reference A11.

e.g. =SUM(Table310[[#Totals],value of A11:[19-Aug]])

Many thanks for any advice.

OK so this has slightly changed.

I now have a from and to drop down boxes for start and end date.

Values are in B3 (start date & B4 end date):
[TABLE="width: 224"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]From Date[/TD]
[TD]11-Mar[/TD]
[/TR]
[TR]
[TD]To Date[/TD]
[TD]19-Aug

[/TD]
[/TR]
</tbody>[/TABLE]

I put this formula in but doesn't sum?

=SUMIFS(Table310[[#Totals],[11-Mar]:[19-Aug]],Table310[[#Headers],[11-Mar]:[19-Aug]],">="&$B$3,Table310[[#Headers],[11-Mar]:[19-Aug]],"<="&$B$4)

I want it to sum the totals between the 2 dates shown?

many thanks

 

[Melimob]

I can't for the life of me work out why it won't sum.

I've checked all my date formats match and they do.

It just returns a 0 and unsure why?

thanks for any advice.

 

[BGY23]

Hi,
Les assume dates are in col E5:E30, values in col F5:F30 and criteria in F1 and F2 (date from and to). Put this formula in F3

[TABLE="width: 299"]
<colgroup><col width="398" style="width: 299pt; mso-width-source: userset; mso-width-alt: 14555;"> <tbody>[TR]
[TD="width: 398, bgcolor: transparent"]=SUM(IF($E$5:$E$29>=$F$1,IF($E$5:$E$29<=$F$2,$F$5:$F$29)))
[/TD]
[/TR]
</tbody>[/TABLE]

You must press Ctrl Shift and Enter keys together to make it work. You will see{ and } and the start an end if one correctly.

Good luck

 

[Melimob]

Hi,
Les assume dates are in col E5:E30, values in col F5:F30 and criteria in F1 and F2 (date from and to). Put this formula in F3

[TABLE="width: 299"]
<tbody>[TR]
[TD="width: 398, bgcolor: transparent"]=SUM(IF($E$5:$E$29>=$F$1,IF($E$5:$E$29<=$F$2,$F$5:$F$29)))[/TD]
[/TR]
</tbody>[/TABLE]

You must press Ctrl Shift and Enter keys together to make it work. You will see{ and } and the start an end if one correctly.

Good luck

HI BGY23

Thank you so much for posting however it's not calculating correctly?
It is returning a value at least but the wrong figure:

[TABLE="width: 91"]
<colgroup><col></colgroup><tbody>[TR]
[TD]E3 = 11-Mar[/TD]
[/TR]
[TR]
[TD]E4 = 18-Mar[/TD]
[/TR]
</tbody>[/TABLE]

=SUM(IF(Table310[[#Headers],[11-Mar]:[19-Aug]]>=$E$3,IF(Table310[[#Headers],[11-Mar]:[19-Aug]]<=$E$4,Table310[[#Totals],[11-Mar]:[19-Aug]])))

this should be returning 1 as there is a 1 under 18MAR in total row yet it is returning 3?

I'm completely baffled?

thanks for any advice?

 

[Melimob]

HI BGY23

Thank you so much for posting however it's not calculating correctly?
It is returning a value at least but the wrong figure:

[TABLE="width: 91"]
<tbody>[TR]
[TD]E3 = 11-Mar[/TD]
[/TR]
[TR]
[TD]E4 = 18-Mar[/TD]
[/TR]
</tbody>[/TABLE]

=SUM(IF(Table310[[#Headers],[11-Mar]:[19-Aug]=]=>=$E$3,IF(Table310[[#Headers],[11-Mar]:[19-Aug]]<=$E$4,Table310[[#Totals],[11-Mar]:[19-Aug]])))

this should be returning 1 as there is a 1 under 18MAR in total row yet it is returning 3?

I'm completely baffled?

thanks for any advice?

I'm still trying to solve this. I've hunted around forums to see why this is just returning a zero and have converted my dates to serial date using > Data > Text to Column > Finish option and see no joy:

=SUMIFS(Table310[[#Totals],[11/03/2019]:[19/08/2019]],Table310[[#Headers],[11/03/2019]:[19/08/2019]],">="&$B$3,Table310[[#Headers],[11/03/2019]:[19/08/2019]],"<="&$B$4)

Can you not do the sumifs combined with table total row and/or column headers?

thanks

 

[BGY23]

Can you post a sample of your data. Not a workbook as I'm not allowed to download them just a few columns and rows should do. Something I can work with. Also the result you expect to see

 

[Melimob]

Can you post a sample of your data. Not a workbook as I'm not allowed to download them just a few columns and rows should do. Something I can work with. Also the result you expect to see

sure and thank you for replying/trying to help:

[TABLE="width: 536"]
<colgroup><col><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]11-Feb[/TD]
[TD]18-Feb[/TD]
[TD]25-Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bangladesh[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]11-Feb[/TD]
[/TR]
[TR]
[TD]Malaysia [/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD]18-Feb[/TD]
[/TR]
[TR]
[TD]zTBC[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zTBC[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zTBC[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zTBC[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]From Date to End Date (as per selection)[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As per above, if user selects start date on right and end date, the value should sum the total row based on from/to column dates.

So in above example would return 3.

this is just not working and returning 0?