Count # of cells b/w cells ...
 

Hello,

I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0
0 0 0 0 0 7 etc.
The number of zero's between the 7's is random. I want a formula that
would count the number of zeros between the 7's.

Thanks,
Ari Bari


--
AriBari
------------------------------------------------------------------------
AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043
View this thread: http://www.excelforum.com/showthread...hreadid=388069

Assume A5:A20 is the data, try this:

B5 = A5+B4 (copy formula down)

Now make a table with 2 columns. First column will be 7, 14, 21, 28,
35, etc. and second column will be:

COUNTIF(B5:B20,<First column)-1


Hope this helps.



AriBari Wrote:
Hello,

I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0
0 0 0 0 0 7 etc.
The number of zero's between the 7's is random. I want a formula that
would count the number of zeros between the 7's.

Thanks,
Ari Bari


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388069

Thanks, Morrigan - it worked.

I came across a new problem. Let's say I have two columns.
Column A: AAABBCAABBCCCABC etc.
Column B: 00000001111111222 etc.

The numbering increases when the series starts repeating in column A.

I only want to count the A's, B's etc for the same number in column B.

I know I can easily do it with a Pivot table, but I would like to try
avoid that.

Thanks again,
Ari Bari


--
AriBari
------------------------------------------------------------------------
AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043
View this thread: http://www.excelforum.com/showthread...hreadid=388069

Assume Column A1:A20 & B1:B20 is your data, make a helper column C:

C = CONCATENATE(A,B)

Now make a table of the side again with 4 Columns, headers will be:
E1 = A
F1 = B
G1 = C

D2:D## will be 0, 1, 2, 3, 4, etc.

E2 = COUNTIF($C$1:$C$20,CONCATENATE(E$1,$D2)) (Copy across and
down)


Hope it helps.



AriBari Wrote:
Thanks, Morrigan - it worked.

I came across a new problem. Let's say I have two columns.
Column A: AAABBCAABBCCCABC etc.
Column B: 00000001111111222 etc.

The numbering increases when the series starts repeating in column A.

I only want to count the A's, B's etc for the same number in column B.

I know I can easily do it with a Pivot table, but I would like to try
avoid that.

Thanks again,
Ari Bari


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388069

Hello Morrigan,

Again, i worked. In the meantime I found an advice in the "Sumif and
countif" posting and came up with a sumproduct formula. if my column a
and b as before, and criteria in column c and row 4, I put this formula
in cell c6:

=SUMPRODUCT(($a$1:$a$3500 =$c5)*($b$5:$b$3500 = d$4))

across and down


Thanks,
Ari Bari


--
AriBari
------------------------------------------------------------------------
AriBari's Profile: http://www.excelforum.com/member.php...o&userid=25043
View this thread: http://www.excelforum.com/showthread...hreadid=388069