Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Adding Cells but starting again after 8 zero's.

0

I am summing a column but if 8 consecutive cells are zero I would like it to return a zero total and then start adding again. The purpose being Statutory Sick Pay paid period zero's after 8 weeks of not claiming. So I can see in T100 the employee has had 17 weeks SSP  he then isn't sick for 12 weeks so his SSP paid to date should return to zero but because the sum I have is =SUM(S$6:S59) it is just totalling the column. I hope someone can help. Thank you.

Answer
Discuss

Discussion

I am a little confused, if everything should reset after 8 weeks then why not just SUM() the last 8 weeks instead of the entire column?
don (rep: 1989) Jul 11, '18 at 11:57 am
Sorry Don, only if there is a consecutive 8 weeks of zero, s should it reset. If he was to go sick every fourth week (for example) these would be linked and the sum continues as it is when he has not been sick for eight weeks in a row it then starts at zero again. Sorry I hope that makes sense. I want the sum to total the column row by row (weeks) until there are 8 or more weeks with zero then the sum returns to zero. Goodness I've got myself all muddled too. ! 
Terdar (rep: 2) Jul 11, '18 at 1:32 pm
Add to Discussion

Answers

0
Selected Answer

Please try the formula below in your cell T112 and copy up/down from there.

=IF(COUNT(OFFSET(INDIRECT(ADDRESS(ROW(),18)),-8,0,8))>0,$T111,0)+SUM($R112)

Note that the number 18 in the ADDRESS function specifies column R (A = 1).

The above formula will throw an error if entered in rows 1:8 and it may produce a wrong result when entered in rows 9:14 and if one of the column captions is numeric. This could be fixed but it would more than double the formula's size. The better way is to stick with your existing SUM() in rows 6:14 and start using the above formula only from row 15.

Discuss

Discussion

Variatus, I cannot thank you enough for your response. The formula works and has saved me so much time.You are a genius !
Terdar (rep: 2) Jul 12, '18 at 3:30 am
Terdar, I'm glad I could help. And thanks for the awarded points. If you are observing the leader board you will see that I'm in a tight race and every little help counts. :-)
Variatus (rep: 4889) Jul 12, '18 at 8:39 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login