CountIF?

[amabomber]

I would like to analyze a list of numbers and be able to display how many listings have occured since a number appeared before. In other words : the string 10,15,24,18,19,15 : Looking at 15 would display the number 4. Any ideas?

[ChemistB]

Are these numbers in a single cell or in a row or column?

[amabomber]

I have the numbers are listed in a column.

[NBVC]

I have the numbers are listed in a column.

My formula assumes that your numbers are listed in a column (A1:A6)

Just adjust that range to suit your actual range of numbers.

Also, change D1 to the cell reference containing the number you want to check (e.g. 15 per your sample).

[ChemistB]

Hi NBVC,
I think he/she's looking at a running formula down column B which looks to the left and says when that last appeared. I modified your formula a bit to see if it worked for that and works when there are only duplicates but not for triplicates (I added another 15 and the second 15 gives an incorrect value of 7 but the 3rd one is right at 3). Take a look and see if you can modify the formula.

[NBVC]

One way:

=MATCH(2,(1/(A1:A6=D1)))-MATCH(2,(1/(A1:INDEX(A:A,MATCH(2,(1/(A1:A6=D1)))-1)=D1)))

where A1:A6 is the whole range of numbers and D1 contains the number to evaluate (e.g. 15)

Note: The formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

[NBVC]

How about in B2, CSE confirmed and copied down?

=IF(COUNTIF($A$2:A2,A2)=1,0,ROW()-MATCH(2,(1/($A$2:INDEX(A:A,ROW()-1)=A2)))-1)

[ChemistB]

As our friends across the pond would say, "Works a treat." Nice one, NBVC.