# Counting till a subject is false

#### RadioRob

##### New Member
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!!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Aladin Akyurek

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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
CDEFGHIJ
111101011
2
322
4
Sheet1

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

#### RadioRob

##### New Member
ADVERTISEMENT
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

##### MrExcel MVP
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

##### New Member
ADVERTISEMENT
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

##### MrExcel MVP
RadioRob said:
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

##### New Member
=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

##### MrExcel MVP
RadioRob said:
....
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
ABCDEFGHIJKLMNOPQR
111101111101111011
2
32
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?

Replies
4
Views
310
Replies
1
Views
758
Replies
8
Views
532
Replies
1
Views
877
Replies
5
Views
746

Threads
1,195,922
Messages
6,012,313
Members
441,690
Latest member
CyberWrek

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back