Excel issue

Ace Modder · 5 Apr 2016 at 10:42

Hi guys

I have come across a very odd issue with Excel.

My columns are listed in numbers, not in letters. What the hell? and how can i fix it?

Armageus · 5 Apr 2016 at 10:46

1. Click on the File tab at the top of the screen and select Options.
2. Click Formulas.
3. Uncheck R1C1 Reference Style.
4. Click OK at the bottom of the window.

source: https://kb.wisc.edu/helpdesk/page.php?id=781#2010

Confused Stu · 5 Apr 2016 at 11:12

Beaten to it by Armageus, spot on answer that man.

Ace - if you think the column labelling is weird with R1C1 on, try entering a long formula and then turning R1C1 style on! That'll blow your mind at first. For me, though, once you learn to read it then formulae make more sense in R1C1 style as it's much clearer which references are absolute/anchored and which are relative to the formula placement.

Ace Modder · 5 Apr 2016 at 11:20

Thanks to you both.

I've just created this on a spreadsheet I'm using...

Code:

=SUMPRODUCT(--('Sheet1 (2)'!A:A=A2),'Sheet1 (2)'!E:E)

Took me a few mins to do it

Grrrrr · 5 Apr 2016 at 13:58

Any reason you're not using =SUMIFS()?

The SUMPRODUCT method is great for doing a 2-dimensional SUMIF though