• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum column until a specific value is reached in that column

krizziii

New Member
Hi guys,

I know there has been some similar questions here before, but I do not find them applicable to my question here (at least those two I have seen).

Please see attached.

I have a given starting date (column B) and then some additional working days (days excluding weekends and holidays) I need to add on top of that to get no an "End date" (coulmn D). The idea here is to be able to put into column C how many working days there are between each milestone, and then automatically get a timetable.

I have inclded helping columns in colmun F through K, where the Norwegian holidays are included (which are applicable in this case).

My first thought was to use an Offset(Indirect()) formula to locate the cell of the date in the helping columt, and then subtotal to sum the number of weekdays and holidays to be added. However, my problem becomes how to sum the column until the value in column C is reached.

This might be a very bad explanation - please ask questions if that is the case. To be clear; the formula I am looking for should be applied in column D (which should not be the sum as it is currently). I have included the correct dates (which should be the output in column D) in column N.

Best,
Kristian

Edit: the file is updated after a comment by vletm.
 

Attachments

  • Test Chandoo.xlsx
    28.5 KB · Views: 6
Last edited:
Hi vletm,

You are correct, the 9 in C10 should be 7, thanks. It is corrected now.

Kristian
 

Attachments

  • Test Chandoo.xlsx
    28.5 KB · Views: 5
Column N "correct output" does not match column D, which is my question; how do I make those match...

Column N is the correct dates which I want column D to output.
Column C is correct as well.
 
krizziii
eg Friday, 7 June 19 (yellow rows)
above is 'corrected' (7)
below is 'original' (9)
both has 'Correct output' 19-Jun - okay?
Screenshot 2019-06-12 at 18.15.50.png
Which values are 'really correct'?
 
Not sure if you understand the sheet.
Correct output is what all the "date end" cells should look like if we are able to fix the spreadsheet. That is the solution. Hence, that is correct in both cases.

The reason why I changed 9 to 7 is that from 7 June there is 7 working days until 19 of June (which is correct), not 9 days - that would take us to 21 June.

The updated file with 7 is the correct one - everything else should be the same. Please find attached a temporary solution. It shold be done much easier, preferably without helping columns/rows (I have seen it once, but cannot remember how it was done).
 

Attachments

  • Chandoo temporary solution.xlsx
    128.6 KB · Views: 3
krizziii
Yes, I don't understand Your sheet.
I cannot understand, why to show both incorrect and correct values?
Normally, there would be much more incorrect values than ... needed correct values!

But, I try to understand the calendar.

Fri, 7 Jun 2019 + 7
Code:
add  1 - 2 - 3 - 4 - 5 - 6 - 7
day  7, 11, 12, 13, 14, 17, 18
Screenshot 2019-06-12 at 19.06.36.png
Yes, that Your the newest file ... wow!
Term 'much easier' match with it!
I could only close that file ...

You have written
However, my problem becomes how to sum the column until the value in column C is reached.
You have written somewhere
how many or is there unlimited 'working days', which can use?
Without that value ... a challenge.
 
Last edited:
krizziii
Yes, I don't understand Your sheet.
I cannot understand, why to show both incorrect and correct values?
Normally, there would be much more incorrect values than ... needed correct values!

But, I try to understand the calendar.

Fri, 7 Jun 2019 + 7
Code:
add  1 - 2 - 3 - 4 - 5 - 6 - 7
day  7, 11, 12, 13, 14, 17, 18
View attachment 60648
Yes, that Your the newest file ... wow!
Term 'much easier' match with it!
I could only close that file ...

You have written
However, my problem becomes how to sum the column until the value in column C is reached.
You have written somewhere
how many or is there unlimited 'working days', which can use?
Without that value ... a challenge.

Wow, that was a weirdly rude answer when you haven't read my question properly.

I show you the correct values in column N so that you know when you have done everything correctly. As I wrote in my original post: "I have included the correct dates (which should be the output in column D) in column N."

Furthermore, as I wrote in my original post:
" the formula I am looking for should be applied in column D (which should not be the sum as it is currently)."

In other words, I need someone to find a formula to be put in column D in order to output the correct dates - the dates that currently are in column D are WRONG because I use a formula that does not incorporate weekends and holidays: this is what I need help with to incorporate. But, if I did not include coulmn N as a backup, how would you know when you have found the correct formula? One more time: column N is only there for you to know when you have found the right formula to put into column D.

And yes, 7 of June + 7 equals 19 June: 8th is Saturday, 9th is Sunday and Holiday, 10th is Holiday, 15th is Saturday, 16th is Sunday. So from 7th to 11th to 12th to 13th to 14th to 17th to 18th to 19th is SEVEN DAYS. Another proof that you did not read my post(or my attached Excel sheet that even have an own section with holidays and weekends...).

One last time; I need a formula in cell D6 thorugh D15 in order to incorporate holidays and weekends so that 7 of June + 7 working days equals 19th of June - thats the whole point of the question. When you have done that and believe you have found the right formula, please check if your output mathces the dates in column N. If not, your formula is wrong, because column N is the right dates given the inputs I have chosen in column C.
 
krizziii
For me, incorrect values are misinformation - only mess.
If start holiday 7 June and uses 7 working days ... I cannot change my reply. Your way there are eight 'th's ...
I used Your holiday-list. Your used weekend days seems to be Saturday and Sunday - no need those sections.
Still, I have not get values,
which You're looking for,
which You wrote 'my problem becomes how to sum the column until the value in column C is reached.'
or
is there unlimited working days for use?
Sorry, but my 'formula' cannot be wrong ... because I have not used any formulas.
Seems that I cannot try to help You
- held og lykke.
 
Does this at least go some way to meeting your requirement?
The key formula I employ is
=WORKDAY(startDate, workingDays, holidays2019)
 

Attachments

  • Project workdays (PB).xlsx
    36.7 KB · Views: 7
Back
Top