Counting till a subject is false

[RadioRob]

Hello... I'm using an Excel worksheet to keep track of attendence for various events. One of the stats I need to know is what the person's attendence streak is.

In my excel worksheet, I have each person in a row and the individual dates in columns. For each time they attend, I've entered a "1" and for each absence, a "0".

What I need to know is how I can have Excel start counting a row each of the 1's and add them until it reaches a 0. Then it should reset the count and begin again.

Could anyone *please* help me? I would be most grateful. My e-mail address directly is ryates1981@hotmail.com

Thanks!!

 

[Aladin Akyurek]

=IF(COUNTIF(B4:BA4,0),SUM(INDEX(B4:BA4,MAX((B4:BA4=0)*(COLUMN(B4:BA4)))-(CELL("Col",B4)-1)):INDEX(B4:BA4,MATCH(9.99999999999999E+307,B4:BA4)-CELL("Col",B4)+1)),SUM(B4:BA4))

Well I entered it exactly as you have it here and hit Ctrl+Shift+enter and it didn't even add the 1's together. My goal is to get it as if attendence was 1 1 1 0 1 1 1 1 1 0 1 1 1 1 0 1 1

The result would equal 2 because the last 0 reset the count and only had a sum of 2 after it.

I appreciate all and any help

It should be (confirmed with control+shift+enter)...

=IF(COUNTIF(B4:BA4,0),SUM(INDEX(B4:BA4,MAX((B4:BA4=0)*(B4:BA4<>"")*(COLUMN(B4:BA4)))-(CELL("Col",B4)-1)):INDEX(B4:BA4,MATCH(9.99999999999999E+307,B4:BA4))),SUM(B4:BA4))

aaCount1AfterLatest0 RadioBob v2.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N
2		3
3
4	1	1	1	1	1	1	1	0	1	1	1
5
6
Sheet1 (2)

 

[RadioRob]

=COUNT(1:1)-MATCH(1,IF(1:1="","",IF(1:1=0,0,"")))

I have 9 folks that attend this particular function and I need to know if they have attended a function in consecutive order. I tried the above formula but what I got was #N/A. I think I also may have said I'm running Excel 2004 but I was wrong, it's 2003. I don't know if that would make a big difference or not.

If anyone thinks they can get a formula to work by seeing my sheet, please let me know and I would be happy to email you a file with the sheet on it.

The formula - =IF(COUNTIF(B2:BA2,0),SUM(INDEX(B2:BA2,MAX((B2:BA2=0)*(COLUMN(B2:BA2)))-CELL("Col",B2)+1):INDEX(B2:BA2,52)),SUM(B2:BA2))

worked great until a few weeks ago when I created a new worksheet and tried to implement it. Now all lit does is keep a running grand total until I enter a 0 and then it never adds anything again. I am using Control+Shift+Enter to enter it.

Again, thanks for all the help so far.

 

[Yogi Anand]

Hi RadioRob:

If your formula worked before and does not work anymore, it is quite possible that ...

1. you have made some changes to the spreadsheet that has meesed up the formula

2. you are trying to use the formula in a situation that the formula did not cover

or such other things. So you may have to check where the real problem lies.

Back to your new question ...

I have 9 folks that attend this particular function and I need to know if they have attended a function in consecutive order. I tried the above formula but what I got was #N/A.

Please show us how your data is laid out -- use HTMLmaker (Colo's Cool utility) to post your data on the board -- so we can see what you are working with.

 

[Aladin Akyurek]

=IF(COUNTIF(B4:BA4,0),SUM(INDEX(B4:BA4,MAX((B4:BA4=0)*(COLUMN(B4:BA4)))-(CELL("Col",B4)-1)):INDEX(B4:BA4,MATCH(9.99999999999999E+307,B4:BA4)-CELL("Col",B4)+1)),SUM(B4:BA4))

Well I entered it exactly as you have it here and hit Ctrl+Shift+enter and it didn't even add the 1's together. My goal is to get it as if attendence was 1 1 1 0 1 1 1 1 1 0 1 1 1 1 0 1 1

The result would equal 2 because the last 0 reset the count and only had a sum of 2 after it.

I appreciate all and any help

Here is a shorter (and I presume, correct) formula:

=SUM(INDEX(B4:BA4,MATCH(2,IF((ERROR.TYPE(1/B4:BA4)=2)*ISNUMBER(B4:BA4),1))):BA4)

which you need to confirm with control+shift+enter (not just with enter).