MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting Frequency of Ones


Posted by April on June 13, 2001 11:54 AM

I would like a function that counts the number of repeated numbers within a subset I.e., I have:

11101111110111111101101011111

I would like returned:

3 0 6 0 7 0 2 0 1 0 5

Any clues?


Posted by Aladin Akyurek on June 13, 2001 11:58 AM

April

How is this series stored: In single cell, in a row or column of cells?

Aladin

Posted by gordon on June 13, 2001 7:04 PM

I think I have the answer, extract within a cell


Posted by gordon on June 13, 2001 7:05 PM

I think I have the answer, extract within a cell
It will take a little while..

Posted by April on June 14, 2001 3:30 PM

Aladin:

The numbers are stored in a single column. Thank you for helping.

Posted by April on June 14, 2001 3:31 PM

Gordon, I'm sorry but I do not understand. Thank you for helping.

Posted by Aladin Akyurek on June 14, 2001 9:55 PM

In that case, your problem is similar to: 19876.html

I'll assume that your numbers are in A from A2 on.
In B2 enter: =CELL("address",A2)
In B3 enter: =IF(OR(ISBLANK(A3),A3=0),COUNT(INDIRECT(B2&":"&CELL("address",A2))),IF(ISNUMBER(B2),CELL("address",A3),B2))

Copy down the last formula as far as needed.

Aladin

Posted by April on June 15, 2001 12:59 PM

Thank you very much, Aladin! It works great.