Reset a Counter to zero and continue to count when a specific number is detected.

[DarkestValkryie]

I am looking for a little help here. I'm not a novice, but I am not an expert either. Just need to resolve one issue. I will include as much info as I can during my explanation.

I am trying to make a "days since" tracker that shows the days since someone has delivered a certain thing. (not using dates just week numbers) like displayed in the table below;
[TABLE="class: grid, width: 907"]
<tbody>[TR]
[TD]Store Name[/TD]
[TD]Region[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]Store Total[/TD]
[TD]Annual Total[/TD]
[TD]Days Since Last Appr[/TD]
[/TR]
[TR]
[TD]Erdington[/TD]
[TD]DC10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD] 0[/TD]
[/TR]
[TR]
[TD]Leamington Spa[/TD]
[TD]DC10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD] 21[/TD]
[/TR]
[TR]
[TD]Coventry Alvis[/TD]
[TD]DC10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]3[/TD]
[TD] 7[/TD]
[/TR]
</tbody>[/TABLE]


I have already worked out how to get the <1 to return as 7 and (=>)1 to return as 0 as well as not recognising a blank cell as anything, I just need to find a way to reset the counter every time (=>)1 is detected.

My formula so far is this (be warned its long)

=IF(ISBLANK(D6),0,IF(D6<1,7,0))+IF(ISBLANK(E6),0,IF(E6<1,7,0))+IF(ISBLANK(F6),0,IF(F6<1,7,0))+IF(ISBLANK(G6),0,IF(G6<1,7,0))+IF(ISBLANK(H6),0,IF(H6<1,7,0))+IF(ISBLANK(I6),0,IF(I6<1,7,0))+IF(ISBLANK(J6),0,IF(J6<1,7,0))+IF(ISBLANK(K6),0,IF(K6<1,7,0))+IF(ISBLANK(L6),0,IF(L6<1,7,0))+IF(ISBLANK(M6),0,IF(M6<1,7,0))+IF(ISBLANK(N6),0,IF(N6<1,7,0))+IF(ISBLANK(O6),0,IF(O6<1,7,0))

This is per cell in the total Column and relates to that row number.

I just need to add the reset count feature to it. Macros are enabled so if I need to insert code that is not going to be a problem I just need it to work and to work in a single cell. I have a grid that does the count for me but that's no use to me. It has to be contained in 1 cell.

Many Thanks in advance.

EDIT:
Also, If I need to use the existing table that works it out across multiple cells as a base for the formula, in order to contain it in one cell then that is fine.

 

[Scott T]

Try

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Store Name	Region	1	2	3	4	5	6	7	8	9	10	11	12	Store Total	Annual Total	Days Since Last Appr
2	Erdington	DC10	0	0	0	1	1	1	1						3	3	0
3	Leamington Spa	DC10	0	0	0	1	0	0	0						3	3	21
4	Coventry Alvis	DC10	0	0	0	1	1	1	0						3	3	7
Sheet6

Cell Formulas
Range	Formula
Q2	=IFERROR(COUNTIF(INDEX(A2:N2,LOOKUP(2,1/(C2:N2=1),COLUMN(C2:N2))):N2,0)*7,COUNTIF(C2:N2,0)*7)
Q3	=IFERROR(COUNTIF(INDEX(A3:N3,LOOKUP(2,1/(C3:N3=1),COLUMN(C3:N3))):N3,0)*7,COUNTIF(C3:N3,0)*7)
Q4	=IFERROR(COUNTIF(INDEX(A4:N4,LOOKUP(2,1/(C4:N4=1),COLUMN(C4:N4))):N4,0)*7,COUNTIF(C4:N4,0)*7)

 

[DarkestValkryie]

Scott you are an amazing individual. Thank you so much!! you have no idea how long I have been working on this sheet!!! You have my utmost gratitude. Thank you.

 

[DarkestValkryie]

New question, same topic. Say I have 4 sheets, one for each quarter, and I want the count from the previous sheet to follow onto the next sheet and then continue counting like before. What formula would I need to put in Q2 on Sheet2 in order to continue with the count whilst taking the previous sheets final result into account??

 

[DarkestValkryie]

ignore that last one I have figured something out to make it work. But how do I make it count a number >=1 not just 1.

 

[Scott T]

Does this do what you want?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Store Name	Region	1	2	3	4	5	6	7	8	9	10	11	12	Store Total	Annual Total	Days Since Last Appr
2	Erdington	DC10	0	0	0	1	1	1	7						3	3	0
3	Leamington Spa	DC10	0	0	0	2	0	0	0						3	3	21
4	Coventry Alvis	DC10	0	0	0	1	1	2	0						3	3	7
Sheet6

Cell Formulas
Range	Formula
Q2	=IFERROR(COUNTIF(INDEX(A2:N2,LOOKUP(2,1/(C2:N2>=1),COLUMN(C2:N2))):N2,0)*7,COUNTIF(C2:N2,0)*7)
Q3	=IFERROR(COUNTIF(INDEX(A3:N3,LOOKUP(2,1/(C3:N3>=1),COLUMN(C3:N3))):N3,0)*7,COUNTIF(C3:N3,0)*7)
Q4	=IFERROR(COUNTIF(INDEX(A4:N4,LOOKUP(2,1/(C4:N4>=1),COLUMN(C4:N4))):N4,0)*7,COUNTIF(C4:N4,0)*7)

 

[DarkestValkryie]

No this just returns 0 and doesn't count 0 as 7. Would probably be a better idea for it to detect between 1 and 5 as the reset point, rather than more than. Thank you for your help btw it is very much appreciated.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...t-counter-for-equal-to-and-more-than-0-a.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.