Sum Column B Based On Given Sum In Column A

pejay

New Member
Joined
Jan 24, 2017
Messages
8
Hi,

I would really like some help with this problem please. In column A (which is a helper column) I have a list of numbers from 1 - 4. These represent number of days after I have subtracted a list of dates in another column. Column B is another helper column, using nested IFs to divide the total between the original 2 dates (1 - 4 days) into the total for single days.

I'm now stuck because I can't find a way to sum the numbers in col B every time 7 (days) is reached in col A.

The overall objective is to be able to display the totals for each week. They are currently a mixture of every 1 - 4 days and don't always correspond with whole weeks. In the example below, the answer I need is 198.8. (1 day 70.9 + 1 day 58.3 + 2 days 19.9 (x2) + 1 day 19 + 2 days 5.4 (x2).

This is already getting quite messy so I'm not keen to introduce anymore helper columns. If there is a better way to achieve what I need, I'm quite happy to start again from scratch.

Many thanks


1596300520167.png
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
We can see that 20 Which is 14 days away from 06/05/2020 and which is missing. So what should i total for 20/05/2020
 
Upvote 0
Yes this is the problem I am having. So for the week ending 20/05, it should be 30.4 + 17.7 + 12.7 + to get the final day (20th) it would be the total for the 23/05 (23.7 divided by the 4 days between the 19th and 23rd giving 5.9 for the missing day. The total should be 66.2.
 
Upvote 0
Book1
ABCDEF
1
2DayDatesSum
306/05/202006/05/2020 -
407/05/202070.913/05/2020 198.90
508/05/202058.320/05/2020 66.73
610/05/202039.927/05/2020 23.48
711/05/20201903/06/2020 -
813/05/202010.810/06/2020 -
916/05/202030.417/06/2020 -
1018/05/202017.724/06/2020 -
1119/05/202012.701/07/2020 -
1223/05/202023.708/07/2020 -
1325/05/20205.7
14
Sheet3
Cell Formulas
RangeFormula
E3:E12E3=$A$3+(ROW(A1:A10)-ROW(A1))*7
F3:F12F3=SUM((E3>=(ROW(INDIRECT($A$3&":"&$A$13))))*(E2<ROW(INDIRECT($A$3&":"&$A$13)))*INDEX(IFERROR(($B$3:$B$13)/(($A$3:$A$13)-($A$2:$A$12)),0),IFERROR(MATCH(ROW(INDIRECT($A$3&":"&$A$13)),$A$3:$A$13,0),MATCH(ROW(INDIRECT($A$3&":"&$A$13)),$A$3:$A$13,1)+1)))
Dynamic array formulas.
 
Upvote 0
Yes this is the problem I am having. So for the week ending 20/05, it should be 30.4 + 17.7 + 12.7 + to get the final day (20th) it would be the total for the 23/05 (23.7 divided by the 4 days between the 19th and 23rd giving 5.9 for the missing day. The total should be 66.2.

I suppose it should be 66.73 Instead of 66.2

and Please confirm the formula with control+Shift+Enter and Not Just Enter
 
Upvote 0
Thanks for the formulas. Yes it should be 66.73, don't know how I got 66.2!

I haven't got it to work yet, I'm sure I must be doing something wrong but I can't figure out what. The formula in E3 returns 06/05/2020, but when I copy it down to E12 it shows all the same date.
 
Upvote 0
I knew to use control+shift+enter with the formula in column F, but wasn't sure about the one in column E. As it happens I had tried it just to be sure and still I get 06/05/2020 in every cell. Also the long formula at the moment is returning all 0.00 results.

I am using Office 2016 by the way.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top