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

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.

Originally Posted by AriBari

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

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

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.

Originally Posted by AriBari

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

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