# Thread: CountIf on multiple fields (Excel 2003)

1. ## 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. ## 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. ## 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. ## 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
•