# Sum Column B Based On Given Sum In Column A

#### pejay

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### pejay

##### New Member
Where I said col A and B, I should have said C and D.

#### CA_Punit

##### Well-known Member
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
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

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

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

Replies
10
Views
173
Replies
6
Views
137
Replies
2
Views
110
Replies
0
Views
87
Replies
31
Views
597

1,141,478
Messages
5,706,610
Members
421,460
Latest member
Taamrak

### 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?

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