# COUNTA problem

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• Problem:
I am using the formula =INDEX(G3:G12,COUNTA(G3:G12)) to extract the last number from a column of numbers.
It will only work when all 10 cells in the column are filled.
However, I need it to work no matter how many cells are filled. The cells G3:G12 contain formulas of the type: =IF(B5="","",G4).
Why will it not work? How do I make it work?

• Re: COUNTA problem

Try this:

Code
``=LOOKUP(9.99999999999999E+307,G3:G12)``

Bruce :cool:

• Re: COUNTA problem

Many thanks, skywriter. I don't see why it works, but it works!

• Re: COUNTA problem

My pleasure, thanks for the feedback. :cool:

Let me know if you want to know how it works.

I'll explain it to you.

Bruce :cool:

• Re: COUNTA problem

Hi,
Yes, I would like to know how it works; the 9.99999999999999E+307 bit is mad!
regards,
Nioc

• Re: COUNTA problem

That's the largest number that Excel can handle, from what I've read.

Lookup will either find that number or return the last number it finds that is smaller.

So it will always return the last number in the range it is looking at, or the first instance of that very large number, if it's in one of the cells.

Excel specifications and limits.

Bruce :cool:

• Re: COUNTA problem

As I said; elegant.
Thanks, skywriter!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!