# Using a formula only to last row of data

#### dave3009

##### Well-known Member
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

Code:
``=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

Code:
``=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

Dave

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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)

That is sooo cool

Thanks again jonmo1

KR

Dave

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

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...

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

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

I would use MAX

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

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?

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

Replies
11
Views
874
Replies
3
Views
565
Replies
6
Views
751
Replies
6
Views
479
Replies
3
Views
318

1,196,487
Messages
6,015,498
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back