Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    Aladin
    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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(...).

    Aladin
    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Microsoft MVP - Excel

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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. #14
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Summing Problem (XP SP-2)

    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.
    Microsoft MVP - Excel

  15. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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
  •