Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Help! I need a formula!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's the situation, for example sake sheet one has the following data columns:

    Name Account Status
    Joe 1234 A
    Joe 7896 A
    Joe 5784 D
    Joe 1432 P
    Joe 3285 A
    Mary 9999 A
    Mary 4444 A
    Mary 1964 D

    On sheet 2, I need to list each "Name" and count how many "A" (active status code) appear for each name and then how many "D or P" (inactive status codes) appear for each name.

    For the above example:

    Name Active Inactive
    Joe 3 2
    Mary 2 1

    Can anyone advise me on this? Thanks!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use a PivotTable. The Status field can be grouped into 'Active' and 'Inactive' categories. The results look something like this...

    Count of Name Status
    Name Active Inactive
    Joe 3 2
    Mary 2 1

    Note: You'll have to view these results in edit mode to see proper spacing.

    [ This Message was edited by: Mark W. on 2002-03-22 09:17 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Any advice for someone not familiar with pivot tables? Sorry.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    With Name in E2
    =SUMPRODUCT(($A$2:$A$9=E2)*($C$2:$C$9="A"))

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 09:12, kopernikuz wrote:
    Any advice for someone not familiar with pivot tables? Sorry.
    See my edited response above.

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've never used a pivot table before... trying it now, and I can kind of see how it would help, but am having trouble figuring out how to define the columns and counts

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wait, think I got it to work... however, in using Pivot tables... I want to add one more field. How do I do it?

    I want to calculate the difference between active and inactive, to get a net and include it in the table. What steps do I take to add this column as well

    Name Active Inactive Var
    Joe 3 2 1
    Mary 2 1 1

    I got the name and status columns working... but how do I add this last one?

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 09:28, kopernikuz wrote:
    I've never used a pivot table before... trying it now, and I can kind of see how it would help, but am having trouble figuring out how to define the columns and counts
    Hi kopernikuz,

    Don't give up! Pivot Tables are one of the best features of Excel, and it would be well worth your time and effort to learn how to use them.

    http://www.cpearson.com/excel/pivots.htm
    is a great resource to get you started.

    Also, I think the MrExcel site proper has some nice tips for the advanced features of PivotTables.

    Good luck,
    Jay

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 09:35, kopernikuz wrote:
    Wait, think I got it to work... however, in using Pivot tables... I want to add one more field. How do I do it?

    I want to calculate the difference between active and inactive, to get a net and include it in the table. What steps do I take to add this column as well

    Name Active Inactive Var
    Joe 3 2 1
    Mary 2 1 1

    I got the name and status columns working... but how do I add this last one?
    I think I can get you fairly close...

    1. Add another "Count of Name" to the PivotTable's DATA area.
    2. While still in the Wizard double-click this new DATA field button, press the [Options>>] button, choose Show data as "Difference From" with a Base field of "Status2" (your grouped status code) and a Base item of "Inactive".
    3. This will add 2 new columns to your PivotTable. Hide the 2nd column.
    4. Change the formatting of the cell containing "Active" in the 3rd column to ;;;"Var"
    5. Change the formatting of the cell contain "Count of Name2" to ;;;

    Note: You'll need to Enable Selection (see the PivotTable | Select menu on the PivotTable toolbar) to preserve this formatting after Refresh.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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