Using cell content as reference in formula

  • Thread starter Thread starter Jarrod Price
  • Start date Start date
J

Jarrod Price

I am a beginner with Excel, and a little lost. When creating a formula, is
it possible to use the content of a cell, without creating labels, to refer
to the cell, or group of cells in which the content appears? For example,
in countless places in my spreadsheet, the text "Total" appears in a cell,
refering to a numeric value, 4 columns to the right in the same row. I
would like to create a formula that adds all the values, wherever they
appear 4 columns to the right of the cell containing the text "Total". To
take this one step further, I would like to create a second formula that
does the same thing, but adds these values across multiple spreadsheets. I
realize that I can manually select these cells using the SUM function, but
the values appear too frequently throughout my spreadsheets for this to
work.
 
Jarro

You don't say, but if "total" is in the same column (say A) then the formul
=SUMIF(A1:A3,"total",E1:E3
will total those values in column E where A has "total"

If "total" is staggered, say in A and B the
=SUMIF(A1:B3,"total",E1:F3
will add the values in E and F where there is the word "total" in A or B

Again, you are not clear, but if these sheet Grand Totals are all in the same place (say E5) then the following will add across sheets
=SUM(Sheet1:Sheet2!E5

Ton

----- Jarrod Price wrote: ----

I am a beginner with Excel, and a little lost. When creating a formula, i
it possible to use the content of a cell, without creating labels, to refe
to the cell, or group of cells in which the content appears? For example
in countless places in my spreadsheet, the text "Total" appears in a cell
refering to a numeric value, 4 columns to the right in the same row.
would like to create a formula that adds all the values, wherever the
appear 4 columns to the right of the cell containing the text "Total". T
take this one step further, I would like to create a second formula tha
does the same thing, but adds these values across multiple spreadsheets.
realize that I can manually select these cells using the SUM function, bu
the values appear too frequently throughout my spreadsheets for this t
work
 
Jarrod,

Try something like

=SUMIF(A1:A100,"Total", E1:E100)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Jarrod
if your 'Total' text only appears in one collumn you can use SUMIF:
=SUMIF(A:A,"Total", E:E)

HTH
Frank
 
Back
Top