CODEWITHSUNDEEP
excelexcel-formula
Ramzes
Ramzes

Reputation: 93

Excel formula: How to refer a cell by its column name

How can I change my excel formula if I need to replace direct reference to the cell by the column name of this cell? For example, instead of

SUM(A1:B1)

I want to use something like

SUM({Column1}:{Column2})

I know about structured reference, but I can not convert my excel dataset into named table. Do I have any options?

Upvotes: 0

Views: 3445

Answers (2)

N.Y
N.Y

Reputation: 92

You can also name the "columns". You can do this by selecting the value including the header row and going to Formulas > Create from Selection or Ctrl + Shift + F3 and selecting only the Top row option.

Create Names from Selection

Once done, you can create formulas using the header names instead of cell references.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

This answer assumes that the column name is the content of the header row for that column.

Say we have data like:

enter image description here

and we want to sum part of the row based on certain months, say between Feb and Jun. In B6 enter Feb and in C6 enter Jun and in D6 enter:

=SUM(INDEX(A2:L2,MATCH(B6,A1:L1,0)):INDEX(A2:L2,MATCH(C6,A1:L1,0)))

enter image description here

So by changing B6 and C6 we can change the part of the second row we are summing.

EDIT#1:

  1. If we re-arrange the headers, the formula should adapt to the re-arrangement
  2. The formula I posted was plain-vanilla. It should work on old versions of Excel as well as the current version. I am using the US Locale. You may need to check the spelling of functions and may need to use a ; rather than a , as the field separator character.

Upvotes: 2

Related Questions