Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Counting Function (XL2K SP3)

    I want to use a function to count items in one column based on values in another. For example... here is some sample data...

    Solution Manning, Jean
    Implementation Manning, Jean
    Root Cause Manning, Jean
    Implementation Lawson, Toni R.
    Root Cause Starr, William
    Root Cause Starr, William
    Root Cause Johnston, Marcus

    So I want to know how many items "Manning, Jean" has in Root cause, Solution, and Implementation. I was thinking of COUNTIF but not sure how to put it together so that the criteria all works. Countif only takes one argument and am not quite sure how to nest them (if that is how it has to be done) to get it to work.

    Thanx so much for any assistance that can be rendered.

    Dennis

    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting Function (XL2K SP3)

    Countif/Sumif work with only 1 criteria. You could create a column to combine all the criteria into 1 and then use this as the criteria column for the countif if desired

    An alternate solution is to use array formulas as described by Chip Pearson.

    Bob Umlas also has a good article about this technique.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Function (XL2K SP3)

    If that data is in cells A1:B:100, then the following formula will count what you asked:

    <pre>=SUMPRODUCT(--(A2:A101="Root Cause"),--(B2:B101="Manning, Jean"))
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Function (XL2K SP3)

    Dennis,

    Attached is a one sheet workbook giving various counting examples. These samples originated from John Walkenbach.

Posting Permissions

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