# count number of times a value appears in a column

This is a discussion on count number of times a value appears in a column within the Excel Questions forums, part of the Question Forums category; Dear all Is there a way of counting how many times something appears in a column before the value changes. ...

1. ## count number of times a value appears in a column

Dear all

Is there a way of counting how many times something appears in a column before the value changes.
below NO appears 10 times in a row, this being the max number of times in a row.

yes
yes
yes
yes
no
no
no
yes
no
no
no
no
no
no
no
no
no
no
yes
yes

Thanks

Dave

2. ## Re: count number of times a value appears in a column

Hi,

Perhaps try:

Sheet1
A B C D
1 List Value Max consecutive count
2 yes no 10
3 yes
4 yes
5 yes
6 no
7 no
8 no
9 yes
10 no
11 no
12 no
13 no
14 no
15 no
16 no
17 no
18 no
19 no
20 yes
21 yes
Excel 2010
Array Formulas
Cell Formula
D2 =MAX(FREQUENCY(
IF(A2:A21=C2,ROW(A2:A21)),
IF(A2:A21<>C2,ROW(A2:A21))
)
)
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
Note:

3. ## Re: count number of times a value appears in a column

Originally Posted by SQUIDD
Dear all

Is there a way of counting how many times something appears in a column before the value changes.
below NO appears 10 times in a row, this being the max number of times in a row.

yes
yes
yes
yes
no
no
no
yes
no
no
no
no
no
no
no
no
no
no
yes
yes

Thanks

Dave
Control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(A2:A23="no",ROW(A2:A23)),IF(A2:A23="yes",ROW(A2:A23))))

4. ## Re: count number of times a value appears in a column

thanks guys

that worked great, just one more thing, can you count how many times the number of times a maximum is reached and can i use the same formula but use the large function for finding secon highest etc.

i guess i will just try that one.

thanks again

Dave

5. ## Re: count number of times a value appears in a column

Originally Posted by SQUIDD
thanks guys

that worked great, just one more thing, can you count how many times the number of times a maximum is reached and can i use the same formula but use the large function for finding secon highest etc.

i guess i will just try that one.

thanks again

Dave
 Data Count yes 2 yes List no 4 no 2 no no yes no no

C2, control+shift+enter, not just enter:
Code:
```=SUM(IF(FREQUENCY(IF(A2:A23="no",ROW(A2:A23)),
IF(A2:A23="yes",ROW(A2:A23)))>1,1))```

C4, control+shift+enter and copy down:
Code:
```=IF(ROWS(\$C\$4:C4)<=\$C\$2,LARGE(FREQUENCY(IF(\$A\$2:\$A\$23="no",
ROW(\$A\$2:\$A\$23)),IF(\$A\$2:\$A\$23="yes",ROW(\$A\$2:\$A\$23))),ROWS(\$C\$4:C4)),"")
```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•