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

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

CA_Punit

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

ADVERTISEMENT

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
934
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
934
Office Version
  1. 365
Platform
  1. 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,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.
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
Top