Complex Summing Problem (XP SP-2)

Topic

New Reply

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?

Reply | Quote

Replies

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))}

Reply | Quote

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

Regards,

Reply | Quote

Try:

=SUMPRODUCT((MOD(ROW($I$119:$I$1192)-CELL(“Row”,$I$119:$I$1192)+0,37)=0)*($I$119:$I$1192))

which is normally entered.

Reply | Quote

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,

Reply | Quote

“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

Reply | Quote

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…

Reply | Quote

Colin,

“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.php?topic=23503&forum=2

Aladin

Reply | Quote

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?

Reply | Quote

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.

“[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”,D1:D10)

is identical to

=CELL(“Row”,D1)

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

The same holds for ROW(…).

Aladin

Reply | Quote

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?

Reply | Quote

Why?

The formula

=SUMPRODUCT((MOD(ROW($I$119:$I$1192)-CELL(“Row”,$I$119:$I$1192)+0,37)=0)*($I$119:$I$1192))

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.

Reply | Quote

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…

Reply | Quote

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

The original poster asked for

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.

Reply | Quote

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…

Reply | Quote