Dynamic summation from a TABLE

[Hrishi]

Hello, I have a table where in my first columns is of dates (there will be multiple entries of same date, which I add daily and number of rows increases) and 6th columns has values. these are the values for respective dates. sample is as follows. [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1/1/17
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]1/1/17
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]2/2/17
[/TD]
[TD]350
[/TD]
[/TR]
[TR]
[TD]3/2/17
[/TD]
[TD]550
[/TD]
[/TR]
[TR]
[TD]3/2/17
[/TD]
[TD]850
[/TD]
[/TR]
</tbody>[/TABLE]

Now I want to make another table which will summaries above figures, so there will be one entry for one date and value will be total of all values achieved on same date in above table. e.g.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1/1/17
[/TD]
[TD]700
[/TD]
[/TR]
[TR]
[TD]2/1/17
[/TD]
[TD]350
[/TD]
[/TR]
[TR]
[TD]3/1/17
[/TD]
[TD]1400
[/TD]
[/TR]
</tbody>[/TABLE]

Now my problem is, I keep adding values in my first table so rows keeps increasing. if I use sumifs formula, reference of last cell in this formula is fix. Dsum I am unable to use Dsumm formula as well, as it gives total of all the dates mentioned in table 2... can somebody help me in this case.

Regards,

 

[AliGW]

Convert the range of cells into a table and then reference the table fields in your SUMIFS - this way the ranges will always be correct.

 

[liveinhope]

I don't use tables much so this is the first time I have used SUMIF with a table but it does work. My summary range in E2:F4 (Headings IN E1:F1) .. you will need to add to this range as there as more range in the table

formula in F2 Don't type the parameters in the formula . Just type "SUMIFS(" (ie formula name and opening bracket) . then point at data you want to summarize in the table you end up with this formula =SUMIFS(Table1[Amount],Table1[Date],E2)

you don't need to adjust this formula as you add /delete rows .. I have added a couple of rows as belo
**NB - YOU will still need to manually add rows to the summary range if the table has "new" dates .. maybe someone else can help automate that bit ***
[TABLE="width: 408"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD]date[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]1/01/2017[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/01/2017[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD]1/01/2017[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/02/2017[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD]2/02/2017[/TD]
[TD]350[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3/02/2017[/TD]
[TD="align: right"]1592[/TD]
[/TR]
[TR]
[TD]3/02/2017[/TD]
[TD]550[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/02/2017[/TD]
[TD]850[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/02/2017[/TD]
[TD]180[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/02/2017[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







[TABLE="width: 132"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD="align: right"]1/01/2017[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD="align: right"]2/02/2017[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD="align: right"]3/02/2017[/TD]
[TD="align: right"]1400[/TD]
[/TR]
</tbody>[/TABLE]

 

[liveinhope]

Convert the range of cells into a table and then reference the table fields in your SUMIFS - this way the ranges will always be correct.

Hi AliGW
the way I read OP's post data is already in a table but his formula is "range" type formula. My post I have started to answer ... are you able to assist with getting the summary range to add "new" dates

 

[AliGW]

I still don't read it that way. We shall have to wait until the OP has confirmed one way or the other.

 

[Hrishi]

Hi, Thanks for your input and discussion. Understanding of "Liveinhope's is right. my date in first table is already in "TABLE" and I have already used the formula given by liveinhope, but the result is all zero. I wonder why?..
In my sheet the formula is =+SUMIFS(Table16[[#Headers],[Value]],Table16[[#Headers],[Date]],G4) but all results are zero,,

In your explanation in formula "Header " is not appearing, while when I select the head it shows it like above.

 

[Hrishi]

Oh yes, I manually removed that Header and typed the formula the way liveinhope shown, and it worked perfectly the way i wanted. Big Thanks a lot to both of you.

 

[liveinhope]

Oh yes, I manually removed that Header and typed the formula the way liveinhope shown, and it worked perfectly the way i wanted. Big Thanks a lot to both of you.

Suggest that you don't type the formulas - much easier to select the data you need and excel will "translate" your selection for you

NB Just remember that at the moment you will still need to maintain the dates in your summary table manually as your table expands to include more dates

I don't know how to automate that bit .. hopefully someone else can help you there