Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Sum (97,2000)

    I know that Excel 97's Conditional Sum Wizard does not work with Merged Cells. What about Excel
    2000? Is there a way around this limitation. Have a 4000 line spreadsheet that has 3 rows per merged cell that I would like to do conditonal summing on.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (97,2000)

    Hi Thomas,

    This may not answer your question, but may help somewhat!

    Many of us Excel users dislike the Merge Cells facility because of the limitations it imposes in areas such as you have found, and when copying cells etc. What I prefer to do is to use Format, Cells, Alignment, and Centre across Selection. This has very much the desired effect of the Merge Cells command, by spreading a value across several cells, but does not have the limitations.

    Good Luck!

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (97,2000)

    Can you use the the Conditional Sum wizard before mereging the cells, and then once your formula is in place merge the cells.

    If that does not help, you can build the formula manually. If you need some help with that post back with the details.

    Andrew C

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (97,2000)

    Unfortunately the formatting of spreadsheet is dictated by a report format. Is there a quick way to remove the merging, delete the empty rows, do the conditional summing, and then get format back to normal? In any case, being able to do conditional summing is a requirement, even if I have to do it
    manually.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (97,2000)

    Probably your quickest way would be to set up some data on a blank worksheet which reflects the range and data you are working with, but without the merge formatting. Then use the wizard on that sheet to insert the formula in the corresponding cell that you are using on the working sheet.

    When you are satisfied with the formula, copy it and paste it into the cell where you actually require the result to be.

    When you have pasted the formula you will need to place the cursor in the formula bar and then press Ctrl-Shilft and Enter at the same time, as the formula will be an array formula. (i.e. it twill be enclosed in {}).

    Andrew C

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

    Re: Conditional Sum (97,2000)

    An option is not to use the Conditional Sum Wizard. Instead, try to devise the required formulas yourself directly where they are needed.
    Microsoft MVP - Excel

Posting Permissions

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