Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CountIf on multiple fields (Excel 2003)

    My spreadsheet has two fields: Salesman 1 and Salesman 2. I would like to find out how many times two salesmen have worked together.

    For example, I would like to count how many times "Bob" appears in column A (Salesman 1) **and** "Fred" appears in column B (Salesman 2) ie: the combination of the two, not just a straight count.

    How would I do this? Would a variation of CountIf work?

    Thanks,

    JoeK

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIf on multiple fields (Excel 2003)

    You can use the SUMPRODUCT() function:
    Formula assumes Bob or Fred could be either salesman 1 or 2.
    =SUMPRODUCT(($A$2:$A$9="Bob")*($B$2:$B$9="Fred"))+ SUMPRODUCT(($A$2:$A$9="Fred")*($B$2:$B$9="Bob"))

    Table in A1:B9

    <table border 1><td>sales1</td><td>sales2</td><td>Bob</td><td>Stan</td><td>Bob</td><td>Fred</td><td>Stan</td><td>Jim</td><td>Stan</td><td>Fred</td><td>Mike</td><td>Bob</td><td>Bob</td><td>Fred</td><td>Bob</td><td>Mike</td><td>Fred</td><td>Bob</td></table>

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CountIf on multiple fields (Excel 2003)

    Assume the range of datas at A1:B9

    Then,count the number of times "Bob" and "Fred" appears in column A and column B, formula :

    =SUMPRODUCT(--(A2:A9&B2:B9={"BobFred","FredBob"}))

    Regards,
    Bosco

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIf on multiple fields (Excel 2003)

    Thanks. I ended up using SUMPRODUCT. The trick was figuring out that you can't use SUMPRODUCT on an entire column, say A:A. I had to enter $A$1:$A$60000, which did the trick.

    JoeK

Posting Permissions

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