Summing various-length columns

Sheet 1 contains
4
5
6
Sheet 2 contains
1 A
3 B
5 C
7 D
9 E

I'm trying to use LOOKUP on sheet 1 to get an exact match from sheet 2. The LOOKUP formula of=LOOKUP(A1,Sheet2!A:A,Sheet2!B:B) for A1 thru A3 obviously doesn't find a match but puts the values of b,c,d accordingly. Is there a way to use INDEX or MATCH to accomplish this as LOOKUP doesn't appear that it will work ?

Answer again, board screwed up yet again!!

Caleb, here's a macro that will put in your total formula.

Sub Total()
'Written by Barrie Davidson
Dim TopAddress As String
Dim BottomAddress As String

If Selection.Columns.Count > 1 Then
MsgBox ("Select one column only")
Exit Sub
End If
TopAddress = ActiveCell.Offset(1, 0).Address
If ActiveCell.End(xlDown).End(xlDown).Row = 65536 Then
MsgBox ("No data to total")
Exit Sub
End If
BottomAddress = ActiveCell.End(xlDown).End(xlDown).Address
ActiveCell.Formula = "=sum(" & TopAddress & ":" & BottomAddress & ")"

End Sub

Regards,
Barrie

If you want to keep summing in the top row and you have no blanks in the columns in use,

=SUM(INDIRECT(ADDRESS(ROW(A2),COLUMN(A2))&":"&ADDRESS(MATCH(1E+30,A:A),COLUMN(A2))))

I assumed that you'll enter this formula in A1. Adjust this to other columns that you use.

Aladin

=======

Aladin, a very interesting solution. Would you mind explaining....

Thanks Aladin - another question

What if the column is blank first? I then get a circular reference...I'd like the formula to work with the column being empty so then the data can be copied or entered into the column and the formula will already be there working...

the syntax in the MATCH function (sorry pushed enter too soon!)

Re: Thanks Aladin - another question

Just because you wanted to have the totals in top row with a formula for totaling that can cope with dynamically changing range prompted me to device what you've got. If you put this formula in the top row of a column which does not have yet any numbers from 2nd row on, you'll get a dialog informing you about a circular reference. Just give Cancel. That column becomes usable for you forever.

If you find this annoying, we have to change your specs a bit. What do you think?

Aladin

Re: Thanks Aladin - another question

Well, I guess it doesn't matter. What I actually have is the sum function in row one. Then the title of the column in row two. Then the data starts in row three and may go for 10 rows or 1000 rows. I'm trying to set this up for someone who does not know much of Excel (I can change the formulas whenever I need to, but I don't trust this person to do it right), so I want to protect the sum cell and make it "error-proof" for however many rows of data exist in rows 3 through whatever. Wow, thanks for your help. I would never have figured that formula out for myself. : What if the column is blank first? I then get a circular reference...I'd like the formula to work with the column being empty so then the data can be copied or entered into the column and the formula will already be there working... Just because you wanted to have the totals in top row with a formula for totaling that can cope with dynamically changing range prompted me to device what you've got. If you put this formula in the top row of a column which does not have yet any numbers from 2nd row on, you'll get a dialog informing you about a circular reference. Just give Cancel. That column becomes usable for you forever. If you find this annoying, we have to change your specs a bit. What do you think? :

Just a trick...

for when MATCH doesn't find a match for the lookup value, it returns the position of the last value in the range. So, you can't use this trick with very huge numbers, an infrequent case.

Aladin

By the way...

blank cells in between non-blank numeric cells won't do any harm...

Aladin :)

Aladin! again you amaze me..where do you get them from..good work fella!! :)

:)

Hey - a fix for the circular ref error

In case anyone is still reading this...

To fix the situation in the below formula that returns a circular ref error if the whole column is EMPTY, simply add a row right below the formula row and enter zero in it. Then, hide the row and enter the formula in the row above it. This way, there is a 0 value for the formula to add and when you enter the formula, it won't return the circref error.

Re: Hey - a fix for the circular ref error

Well, just because I never hide myself anything in a worksheet, I didn't want to tell you that. A bias, so to speak.

Aladin :)

Re: Hey - a fix for the circular ref error

Yeah, I know what you mean. But I don't have time to figure something else out; someone else will be using this sheet and it will be protected so nothing can get messed up, so I'm not too concerned about it. But generally, I share your dislike for hiding rows, etc.

Thanks again!

Caleb Well, just because I never hide myself anything in a worksheet, I didn't want to tell you that. A bias, so to speak. :)