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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
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
 

pejay

New Member
Joined
Jan 24, 2017
Messages
8
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.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
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.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
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
 

pejay

New Member
Joined
Jan 24, 2017
Messages
8
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.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
and Please confirm the formula with control+Shift+Enter and Not Just Enter
Not Enter to confirm Formula but with Control+Shift+Enter
and Drag the Formula
 

pejay

New Member
Joined
Jan 24, 2017
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,356
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top