Simple (?) Excel question - sum column number

  madwab 07:56 09 Dec 2004
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 2004

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 2004

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.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?