1

I have an Excel 2007 workbook. On one worksheet, I want to add up several regularly spaced values on another worksheet. I can easily do this with a very long sum formula without an array formula but it would be much simpler/nicer/easier to see what's going on if I could use an array formula. Below are a couple of my tries. Both seem to return only the first value out of the 8, instead of the sum of the 8. I am using Ctrl+Shift+Enter. Is there a way to do this? Am I making a silly mistake?

Try Number 1:

=SUM(INDIRECT("'Other Worksheet'!H" & {"12";"30";"48";"66";"84";"102";"120";"138"}))

Try Number 2:

=SUM(OFFSET(INDIRECT("'Other Worksheet'!H12"), {0,18,36,54,72,90,108,126},0))
Improve this question

1 Answer 1

Reset to default
1

First, are your values separated by other number values or not ? If not, you can just use sum(other worksheet!h12:h138), it'll ignore text or null values found in between the figures ou want to add. If yes, are these values identified by a label nearby ? For example, the word "total" in column G ? In this case you could use sum.if((g12:g138;"Total";H12:H138). It'll sum up only the values identified by the word "total" on the left cell beside.

Improve this answer
1
  • It looks like I can use the sumif suggestion, so +1 and thanks. I would still like to know how to use an array formula in general though.
    – GeoffDS
    Apr 24, 2013 at 17:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .