Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count On SubTotals (XL97;SR2)

    I'm having an issue with applying subtotals to the following range of cells.


    <table border=1><td>CurDept</td><td>Name</td><td>PriorDept</td><td>1001</td><td>Andrew</td><td>1001</td><tr><td>1002</td><td>Legare</td><td>1003</td><tr><td>blank</td><td>Charlotte</td><td>1002</td><tr><td>blank</td><td>John</td><td>Blank</td><tr><td>1004</td><td>Dr D</td><td>blank</td></table>

    What I would like to accomplish is to use subtotals to count the CurDept and PriorDept. The issue is with the name John as this line is included or should I say displayed in the subtotal. I would like to not have any line(s) show up where the CurDept And PriorDept = blank.

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Count On SubTotals (XL97;SR2)

    John, I don't understand the question. You can use Data, Subtotals using the Count function to count by CurDept -or- by PriorDept, and you will get an Outline by whichever you subtotal on, but you can't use Data Subtotals for both. Or you can use =COUNTIF(priordept,"1001") functions for both columns, but they won't give you an outline.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count On SubTotals (XL97;SR2)

    John,

    I'm trying to take the easy way out. The subtotal seems to be the easiest solution ie returning the Outline with a count for each change in the CurDept. The Outline would not include rows of data where the CurDept and PriorDept are blank. This may not be the best way to produce a report but I'm open to alternative suggestions.

    I've taken a look at pivot tables and they work but do not product an Outline that is acceptable for reporting purposes.

    Hope this helps clarify things.

    John <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Count On SubTotals (XL97;SR2)

    All I can suggest is to run two separate Data, Subtotals runs, one for PriorDept, one for CurDept. You can't do what you want in one report because the subtotals on the two fields will conflict, that is, over census they will have a many to many relationship. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Count On SubTotals (XL97;SR2)

    Another option to consider is to use Advanced Filter to create a duplicate of the data on the same sheet, say using criteria 1002. Subtotal the original data on 1001, and the duplicate on 1002. Collapse the outlines, highlight both outlines, the use Go to, Special, Visible cells only to paste the results to another part of the sheet. Format and print. Macros can be recorded to do this.
    HTH
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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