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]

On 2002-08-25 10:39, RadioRob wrote:
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!!

Is it not just:

=SUM(attendance-range)

which will give you the number of times a person is recorded as present?

This is of course same as:

=COUNTIF(attendance-range,1)

Both formulas expect that you entered 1's and 0's as true numbers.

 

[RadioRob]

I tried a countif statement... =COUNTIF(F3:V3,1) and it justs keeps a grand running total. I need it to stop counting and reset the count if it hits 0

So 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.

Thanks again for responding!! I appreciate it. Any advice ya could give would be wonderful.

 

[Aladin Akyurek]

On 2002-08-25 11:04, RadioRob wrote:
I tried a countif statement... =COUNTIF(F3:V3,1) and it justs keeps a grand running total. I need it to stop counting and reset the count if it hits 0

So 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.

Thanks again for responding!! I appreciate it. Any advice ya could give would be wonderful.

Let C1:J1 house a bunch of 1 and/or 0's.

The essential formula would be:

=SUM(INDEX(C1:J1,MAX((C1:J1=0)*(COLUMN(C1:J1)))-CELL("Col",C1)+1):INDEX(C1:J1,8))

which must be array-entered.

I believe it needs to be extended to cases where there are no 0's:

=IF(COUNTIF(C1:J1,0),SUM(INDEX(C1:J1,MAX((C1:J1=0)*(COLUMN(C1:J1)))-CELL("Col",C1)+1):INDEX(C1:J1,8)),SUM(C1:J1))

which also must be array-entered.

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Postscript. The number 8 is the number of columns in C1:J1, while computable, expressly hardcoded.

See the figure...

Book8
	C	D	E	F	G	H	I	J
1	1	1	1	0	1	0	1	1
2
3		2	2
4
Sheet1

Aladin
This message was edited by Aladin Akyurek on 2002-08-25 12:04

 

[RadioRob]

I realize this is an old question but lately I've been trying to get it to work in an Excel 2003 sheet and the formula will not add anything after I enter a 0 for non attendance. Can someone help me?

Just in case I've done something wrong I have C&P the formula for my sheet below. Thanks!

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

 

[Zack Barresse]

i know it's probably a dumb question so excuse me if i'm offending, but you are confirming this with Ctrl + Shift + Enter, right?

 

[RadioRob]

No I'm not offended And yes I am using it to confirm. Which is why I don't understand why this isn't working. Once I put in a 0 for attendance it won't start counting again.

 

[Aladin Akyurek]

No I'm not offended And yes I am using it to confirm. Which is why I don't understand why this isn't working. Once I put in a 0 for attendance it won't start counting again.

Rob,

Try...

=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))

which you need to confirm with control+shift+enter instead of just enter.

Please post back whether this does work as intended.

 

[RadioRob]

=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

 

[Yogi Anand]

....
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

Hi RadioRob:

Let us have a look at the following ...

y040517h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	1	1	1	0	1	1	1	1	1	0	1	1	1	1	0	1	1
2
3	2
Sheet12

formula in cell A3 is ... =COUNT(1:1)-MATCH(1,IF(1:1="","",IF(1:1=0,0,"")))

this is an array formula and is to be entered with CTRL+SHIFT+ENTER rather than with just ENTER

Would this do?