A problem using array formula

[caldera55]

Hello, I'm trying to use the array formulas of this post:
http://www.excelforum.com/showthread.php?t=644521
using Excel 2007, but it doesn't work. I don't know what could be happening.

I tried with the three formulas, but nothing.
I put the formula in the place indicated, and then I click with the cursor in the formula bar, and hold down Contrl+Shift, then Enter. Then it tells me an error, and indicate with black that some part of the formula is bad, like in :
=IF(COUNTIF(A$1:A1, A2) = 0, ROW() - 1, ROW() - MATCH(2, 1 / (A$1:A1=A2) ) ) - ROW($A$2) + 2
the "A1, A2" of the first part.

In the Mikerickson's Formula:
=ROW()-MAX(--($B$1:B1=B2)*ROW($B$1:B1))
it gives me an error of "circular reference"

I attached a worksheet with the three formulas.

Thank you for your help,
Caldera

[NBVC]

Not sure what exactly you are trying to do... but you can't put the formula in B2 since it is referenced in the formula.

[NBVC]

Based on your linked post, I think Mike might have meant:

=ROW()-MAX(--($A$1:A1=A2)*ROW($A$1:A1))

in B2, copied down

[caldera55]

Hello NBVC,
I'm trying to obtain the cells that last an element in appear:

A 1
B 2
A 2
C 4
C 1

Here the first A last 1 cell in appear, the B two cells in appear, and the C four cells in appear, but the second A only 2 cells, and not three, because there was another A in appear in the first cell. The second C is just 1 cell in appear.

I tried what you told me over the Mikerickson's formula, but it gives me a "name" error.

Thanks for your help

[NBVC]

#NAME? error occurs usually with syntax error in your formula... did you copy it correctly?

see attached

[caldera55]

Thank you very much, NBVC.
Now it works perfectly. Yes, it worked, but the problem was that I was copying the formula making Ctrl+copy, but I did not traduce it, and it seems my Excel doesn't has learn english yet. I have to put the logic symbol in spanish to make it works, but when I had copy the formula that you put it, I did not translate it, and that was what it makes a Name Error.
It is not the same problem with the other formulas, because I proof to translate it, but it was with the mikerickson formula you give me, that makes a different error. When you send me that worksheet, the program translate it directly.

Thank you very much again,
Caldera