Results 1 to 15 of 15

20021025, 16:14 #1
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Complex Summing Problem (XP SP2)
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?

20021025, 18:54 #2
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Complex Summing Problem (XP SP2)
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))}

20021026, 15:17 #3
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
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.Microsoft MVP  Excel

20021028, 16:56 #4
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
Thank you for your response. I am really pleased to tell you that your formula works perfectly.
Regards,

20021028, 17:00 #5
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
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,

20021028, 19:01 #6
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Complex Summing Problem (XP SP2)
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...

20021028, 21:26 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
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
AladinMicrosoft MVP  Excel

20021028, 21:28 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
"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.htmlMicrosoft MVP  Excel

20021028, 21:48 #9
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Complex Summing Problem (XP SP2)
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?

20021028, 22:28 #10
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
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(...).
AladinMicrosoft MVP  Excel

20021028, 22:48 #11
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Complex Summing Problem (XP SP2)
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?

20021028, 22:56 #12
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
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.Microsoft MVP  Excel

20021028, 23:09 #13
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Complex Summing Problem (XP SP2)
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...

20021028, 23:34 #14
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Summing Problem (XP SP2)
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 singlecell formula. Just enter the formula in a cell outside the range, you'll get the sum of every 37th cell.Microsoft MVP  Excel

20021028, 23:51 #15
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Complex Summing Problem (XP SP2)
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...