Simple (?) Excel question - sum column number

  madwab 07:56 09 Dec 04
Locked

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?

Thanks

  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...

=SUMIF(R[-3]C[-1]:R[-1]C[-1],"fred",R[-3]C:R[-1]C)

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

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

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…