Excel using INDEX to get row from named range causes formula error

Question

I have a very simple formula to get the sum of values in a row:

=SUM(K5:P5)

However, K5 to P5 is the first row in a named range, and I would prefer to reference the named range, so I change it to:

=SUM(INDEX(S2ScoresTotUnitQ1,Row("A1"),0))

But I get a "we found a problem with this formula error". How do I reference individual rows of my named range without getting an error?

Won't that sum all scores in the named range? My named range has multiple rows, and I only want to sum one row. — user3925803, Feb 23 '17 at 13:03

score 3 · Accepted Answer · answered Feb 23 '17 at 13:04

3

Remove double quote from A1 referrence. Means Row("A1") will be Row(A1). So, formula will be as following

=SUM(INDEX(S2ScoresTotUnitQ1,ROW(A1),0))

answered Feb 23 '17 at 13:04

Harun24hr

Thanks! Yes I wanted to use ROW() so that it would be a relative reference when I copy and paste. – user3925803 Feb 23 '17 at 13:29
@Ralph He did a big mistake with `Row(A1)` and you are saying it is not a important part. It was only error of his formula. Otherwise all were okay. – Harun24hr Feb 23 '17 at 15:53
@harun24hr My point is that `1` = `Row(A1)` = `12/12` = `25 -12 / 6 + 17 - 30 * 4 - 20/5 + COLUMN(Z1)+ROW(A25)+VALUE("34")`. Why over complicate the formula and use some function when you can simply use `1` instead? `=SUM(INDEX(S2ScoresTotUnitQ1,1,0))` – Ralph Feb 23 '17 at 19:06

Vityata · Answer 2 · 2017-02-23T15:03:59.223

0

The column in the Index formula is an optional argument, you do not have to pass it, if you do not wish. Thus, something like this should work:

=SUM(INDEX(S2ScoresTotUnitQ1;1;))

Whenever the formula has these [ and ] like here, it is optional argument:

edited Feb 23 '17 at 15:03

answered Feb 23 '17 at 13:08

Vityata

2 Answers2