Simple (?) Excel question - sum column number

  madwab 07:56 09 Dec 04

I need to sum / sumif a column where I have a column number. I don't want to turn R1C1 references on for the workbook. I have thousands of formulae in the sheet and will have hundreds of this sum(column-reference) formula, so to keep things fast I don't want to translate the number into an alphabetic equivalent and use 'indirect' on the result.

Is there a simple way of doing this?


  Chris the Ancient 08:54 09 Dec 04

Just had a go - and I cheated by using the 'insert function (fx)' facility. Because that allowed me to select a range of cells without quoting cell references, I got a formula.

It looked wierd...

I had a column of three entries using names - including 'fred' adjacent to a column of numbers and then used the fx to sumif the name was fred. It looked like...


So, as you can see, the sumif will work on offsets from the result cell and give a real answer without going to the nausea of having to go back to alphanumerid cell referencing!

  madwab 11:21 14 Dec 04

I haven't got my head around this yet, tho' it looks like it has the same problem that I've found with a/n references: R[-3] refers to a row relative to the current row, so if I have a variable containing a row I want to reference, I need to first work out the current row then subtract the two to get a relative reference. I got a kludge solutuion that works, but I'll come back to this when I get time - thanks.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…