Using a formula only to last row of data

[dave3009]

Hi Folks

I use a few formulas that run a various sizes of data. One of my analysis tools currently has a series of SUMPRODUCT's, SUMIF's, COUNTIF's etc etc which check data anything from a day to a year in size (there is no fixed size to them either). So I came up with a fix I use =COUNTA(A:A) and then INDIRECT it in another formula

=SUM(A2:INDIRECT("A"&C2))

Problem I then got is if a few blanks appear in my reference column the lot doesn't total so I then came up with

=COUNTA(A:A)+COUNTBLANK(A1:INDIRECT("A"&COUNT(A:A)))

Which is good and works with the data I use but I was wondering if there was a better way of doing it or even a way of getting a reference to the last row without the use of VBA. This is just incase I ever have large gaps that don't get filled enough to pick up the next lot of COUNTA's

Thanks in Advance


Dave

 

[Jonmo1]

For data that is numbers, you can use
=MATCH(--REPT(9,15),A:A,1)

for text you can ue
=MATCH("zzz",A:A,1)

 

[dave3009]

That is sooo cool

Thanks again jonmo1

KR


Dave

 

[Jonmo1]

Sorry, didn't mean to double post. Can a moderator delete this please?

 

[Richard Schollar]

For data that is numbers, you can use
=MATCH(--REPT(9,15),A:A,1)

Be a bit wary of this as altho Excel is limited to 15 digits' precision, the max value a double can take is enormous ie 9.999E+307 or so...

 

[Jonmo1]

I understand and you're right Richard, But seriously, how often does anyone use a number larger than 999 TRILLION?? I don't even know what comes after Trillion....LOL

 

[dave3009]

Hi Guys

I tried them both and they work beautifully

One thing is not to have 2 types of data in one column

If I put the number 2 in row 14 and a space in 27 they will seperately show 14 and 27 however if in the unlikely event of having text to a certain point then numbers you can't add the two together in a

=MATCH("zzz",A:A,1)+MATCH(--REPT(9,15),A:A,1)

Fashion, I guess just to be on the safe side I will have 3 reference cells one with either formula then an IF to see which is bigger

Superb none the less

KR


Dave

 

[Jonmo1]

I would use MAX

=MAX(MATCH("zzz",A:A,1),MATCH(--REPT(9,15),A:A,1))

 

[brian.wethington]

so what are you trying to say Richard? This is the best way to go as it is the last number that will work with the formula?

=MATCH(--REPT(9,255),A:A, 1)