3

I want to write a formula like =SUM(tab2!A:A) but instead use the column title of A which is say "count". How can I modify this to look more like: =SUM(tab2!"count")?

The reason I want to do this is because I copy and paste a spreadsheet from another source in tab2 and the column referring to "count" may be in a different column. I want the formula to give me the correct calculation as soon as I paste the new spreadsheet by automatically finding the column to sum up by title name.

I must be missing something because this seems like a very basic question and I can't find the answer anywhere...

Thanks for your help!

ru111
  • 813
  • 3
  • 13
  • 27

3 Answers3

2

enter image description here

Check out the name manager for naming ranges :)

Doug Coats
  • 6,255
  • 9
  • 27
  • 49
  • I created names from tab2 and managed to get a formula using the title of the column, but when I replace tab2 with a new spreadsheet with columns ordered differently, the formula doesn't find the new column corresponding to the title (the formula uses the old column which now has a different title). What am I missing? – ru111 Feb 04 '16 at 13:02
  • it may be too early for me, but can you post and example? It might be that the named range is statically associated with the range. Like "this.Range" in the original workbook is associated with ("C:C") which might not be the same in each workbook. – Doug Coats Feb 04 '16 at 13:20
  • Say I have 2 columns "age" and "count". I highlight the two columns and press "Create from Selection" which allows me to calculate '=SUM(count)' where this corresponds to the second row. Great so far. Now, if I have new data where the columns are in the wrong order ("count", "age") and paste this in, the '=SUM(count)' will still calculate the second column (which is now "age"), despite the change in column name. I want the formula to find the column corresponding to the particular name "count", wherever the column is. – ru111 Feb 04 '16 at 13:44
  • Now you'll most likely have to get this done in vba. This post might help, and then you can add a worksheet function to place the sum in the desired location. http://stackoverflow.com/questions/10106465/excel-column-number-from-column-name – Doug Coats Feb 04 '16 at 13:59
2

I like the idea of naming ranges proposed by @Doug, but if the issue is that you are dumping your data in [and you don't know in advance which column is going to be where] and would need to rename your range every time, there are other options - I suggest using OFFSET. OFFSET allows you to dynamically create a range, starting at a particular point and moving down/up / right/left for as many rows and columns as you determine.

In your case, you will need to combine that with a method for searching the columns to see which one says "Count". I am assuming that your column headings are always in row 1. If they aren't [or if they aren't always in row 2, or any row you know in advance]... you can get around that but then I'd recommend you try to make your data more uniform instead of creating unnecessary Excel workarounds.

In total your formula would look like this:

=SUM(OFFSET(A1,,MATCH("Count",1:1,0)-1,ROWS(A:A),1))

What this does is first determine which column the word "Count" is found in, in row 1. Then it subtracts 1 from that number - this now equals the number of columns to the right that it is, from column A. It uses offset to create a reference to that column, for all rows, and sums those rows together.

Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46
1

You didn't say whether you would consider a macro solution. If so, this may work. If the sheet you are pasting into is Sheet2 and the sheet you want the result in is Sheet1, then this macro, if placed in the Worksheet_Activate event of Sheet1 will give you the result as soon as you click on the Sheet1 tab afetr pasting your data into Sheet2:

Private Sub Worksheet_Activate()
Dim r As Range
Dim rCol As Range
Dim rFound As Range
Dim ws As Worksheet
Dim lTotal As Long

Set ws = Sheet2
Set r = ws.Cells
Set rFound = r.Find("count")
If Not rFound Is Nothing Then
    Set rCol = rFound.EntireColumn
    lTotal = Application.WorksheetFunction.Sum(rCol)
End If

Cells(1, 1) = lTotal

End Sub

It does assume there is only one cell with the word "count" in it on Sheet2.

graeme_a_henderson
  • 11
  • 2