How to count number of Xs in the last sequence Xs in a column

Hi prudential,

See if this formula does what you need after using a Helper Column.

=LOOKUP(2,1/($A$1:$A$6="X"),($B$1:$B$6))

Last X in Column Count String Helper.xlsx

Hi,

If you knew the last string of X's was the largest number you might be able to use a Max function but I don't know of a way to do this problem without a helper. Maybe one of the smart Guru's will have a way?

There's no way to start the count from when there was no X?

Somthing similar to this - =LOOKUP(2,1/(A$1:$A$6<>""),$A1:$A$6)="X"

Look at the formula in the helper column that does what I think you mean. Look at the different formula in B1 vs B2. I pulled the B2 column down to the bottom of the Column A rows.

Here are two formulas. Select suitable for you. PL see file.

=SUMPRODUCT(MATCH(2,1/(A1:A6="X"))-MATCH(2,1/(A1:A6<>"X")))

=MAX(SUMPRODUCT(MATCH(2,1/(D1:D6="X"))-MATCH(2,1/(D1:D6<>"X"))),0)

Try this one without helper column

Formula:

Please Login or Register  to view this content.

Hi kvsrinivasamurthy,

Your first formula above doesn't work if the last letter in a column is a "Y". I also can't make the second formula work for me. To test I've changed your formulas to include the entire column A. Then add letters to the bottom of the rows and see if it agrees.

Any improvements to your formulas? Am I missing something?

Originally Posted by AlKey

Try this one without helper column

Formula:

Please Login or Register  to view this content.

AlKey,

That's brilliant, thank you so much. That now solves this other problem as well.

https://www.excelforum.com/excel-for...m-a-range.html