# Thread: Complex Summing Problem (XP SP-2)

1. ## Complex Summing Problem (XP SP-2)

I have a user who has a huge model. On one worksheet of the model she has duplicate layouts for 58 companies, one after the other. The layouts are exactly the same in architecture consisting of 37 rows and 134 columns. The problem is that she now wants to sum cells from each layout in what amounts to a consolidation layout.

The problem is that short of just adding cells (I119+I156+I193...), I can't seem to come up with a formula that works. I tried the following formula:

{=SUM(IF(MOD(ROW(INDIRECT("1:"&COUNT(dataI1)))-1,37)=0,dataI1,""))}

but it results in the #NA error. This same formula works in a test worksheet I set up, so I can only assume it doesn't work here because I am trying to sum more than 30 items, but I'm not sure. (Note: "dataI1" is a defined name that includes cells \$I\$119:\$I\$1192, where I want to add every 37th row, and I have verified that the cell in every 37th row contains a value.)

Anyone have any ideas?

2. ## Re: Complex Summing Problem (XP SP-2)

Not sure about your error, but I tried it with a slightly different array formula and it worked OK for me. I had the total rows immediately below the data, and my formula was:
{=SUM((MOD(ROW(I\$119:I\$1191),37)=MOD(ROW(),37))*(I \$119:I\$1191))}

3. ## Re: Complex Summing Problem (XP SP-2)

Try:

=SUMPRODUCT((MOD(ROW(\$I\$119:\$I\$1192)-CELL("Row",\$I\$119:\$I\$1192)+0,37)=0)*(\$I\$119:\$I\$119 2))

which is normally entered.

4. ## Re: Complex Summing Problem (XP SP-2)

Thank you for your response. I am really pleased to tell you that your formula works perfectly.

Regards,

5. ## Re: Complex Summing Problem (XP SP-2)

Thanks for your reply. Your formula works perfectly, as does Colin's array formula . I think I'm going to have to study the SUMPRODUCT formula in some detail.

Thanks,

6. ## Re: Complex Summing Problem (XP SP-2)

Hi. Was looking at your formula and am puzzled about a couple of things. Do you mean that the formula should be entered in all 37 cells? In which case, won't they all return the same total? Rather than CELL("Row",\$I\$119:\$I\$1192), did you mean to pick up the row number of the cell where the formula is entered? The poster said your formula worked perfectly, so I think I must be missing something(?)
Also, more out of curiosity, why did you use CELL("Row",...) rather than just ROW() in your formula? Aren't they the same? Another question: why the +0 in the middle of your formula. One (final!) question: why use the structure "function1 - function2 = 0" rather than "function1 = function2"?

Questions, questions, questions...

7. ## Re: Complex Summing Problem (XP SP-2)

Colin,

"[S]should [the formula] be entered in all 37 cells?"

No. Details of the formula including the +0 bit is in:

http://216.92.17.166/board/viewtopic...=23503&forum=2

8. ## Re: Complex Summing Problem (XP SP-2)

"I think I'm going to have to study the SUMPRODUCT formula in some detail."

You might want to have a look at the following link:

http://www.mrexcel.com/wwwboard/messages/8961.html

9. ## Re: Complex Summing Problem (XP SP-2)

Thanks. That link does answer my last two questions. It does suggest, though, that the middle part of the formula you posted has a typo, in that you specified the whole range rather than a single cell(?).
How about the question about switching to using CELL("Row",...) rather than sticking with ROW(...). Any significance?

10. ## Re: Complex Summing Problem (XP SP-2)

Colin,

"How about the question about switching to using CELL("Row",...) rather than sticking with ROW(...). Any significance?"

None. It's I guess just a personal thing for opting for CELL(...) . They are equivalent.

"<!t>[he middle part of the formula you posted has a typo, in that you specified the whole range rather than a single cell(?)."

No typo for...

=CELL("Row",D110)

is identical to

=CELL("Row",D1)

The former allows you to say in the larger formula D110 or just Range, which refers to D110.

The same holds for ROW(...).

11. ## Re: Complex Summing Problem (XP SP-2)

But I still think you have a couple of extra dollar signs in the formula you posted. Shouldn't the middle part read:
...CELL("Row",\$I119:\$I1192)..., i.e., the rows should be relative rather than absolute?

12. ## Re: Complex Summing Problem (XP SP-2)

Why?

The formula

=SUMPRODUCT((MOD(ROW(\$I\$119:\$I\$1192)-CELL("Row",\$I\$119:\$I\$1192)+0,37)=0)*(\$I\$119:\$I\$119 2))

has \$I\$119:\$I\$1192 all the way down as I intended.

Define Range as referring to I119:I1192, using the Name Box. You'll get an "absolute" referenced range.

13. ## Re: Complex Summing Problem (XP SP-2)

Still puzzled, I'm afraid. If I enter the formula the way you have it, I get the same answer in all 37 cells. That's actually what I'd expect since if the whole formula is absolute, copying it to another cell would have to give the same answer. ?? I know what you mean about defined ranges being absolute, but don't see the significance to the above...

14. ## Re: Complex Summing Problem (XP SP-2)

You lost me. I don't know what point you're trying to make...

quote

"dataI1" is a defined name that includes cells \$I\$119:\$I\$1192, where I want to add every 37th row, and I have verified that the cell in every 37th row contains a value.

unquote

The formula I proposed does just that.

The formula is a single-cell formula. Just enter the formula in a cell outside the range, you'll get the sum of every 37th cell.

15. ## Re: Complex Summing Problem (XP SP-2)

OK, I think I understand the confusion. I took it that the poster needed summary formulas in the 37 rows beneath his/her data, so that summary total #1 would be the sum of all numbers in relative row 1, etc. You were assuming just one total was required (you were probably correct since the poster said your formula worked fine!).
Anyway, I did discover one thing regarding the use of CELL("Row",...). It behaves differently than ROW(..) when used within the SUMPRODUCT formula. I tried replacing CELL("Row",...) in your formula with ROW(...), and it returns the total of all the numbers rather than just every 37th. FWIW...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•