# 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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### 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
6
Views
89
Replies
0
Views
36
Replies
31
Views
345
Replies
0
Views
160
Replies
8
Views
178

1,133,426
Messages
5,658,731
Members
418,467
Latest member
sc356448

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

### Which adblocker are you using?

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

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