# Thread: Excel 2007, a formula to check multiple rows for a given customer name

1. ## Excel 2007, a formula to check multiple rows for a given customer name

Hi,

Column A = Customer Names (many duplicate entries, because of column B)
Column B = Customer Location (all customers have multiple locations)
Column C = "type1" or "type2"

a customer might have "type1" and "type2" locations

How do we make a formula, for column D, to state if the customer has both types?

something like: "IF Customer Name Has "type1" AND "type2" then say "MIXED" else say "NOT MIXED"

thanks

data would look like:
CustomerAB | 555 street | type1 | mixed
CustomerAB | 500 street | type2 | mixed
CustomerXZ | 111 street | type1 | not mixed
CustomerXZ | 089 street | type1 | not mixed

2. For example:
=IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed")

3. Originally Posted by JoePublic
For example:
=IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed")
Unfortunetly not working.

All of them say "not mixed", but some should say mixed.

data SHOULD look like:
CustomerAB | 555 street | type1 | mixed
CustomerAB | 500 street | type2 | mixed
CustomerAB | 778 street | type1 | mixed

CustomerXZ | 111 street | type1 | not mixed
CustomerXZ | 089 street | type1 | not mixed
CustomerXZ | 019 street | type1 | not mixed

but it says "not mixed" for all.

4. Can you post an actual workbook, please, because that formula should work?

5. It works fine for me, some mixed, some not mixed.

6. Originally Posted by JoePublic
Can you post an actual workbook, please, because that formula should work?
It works! Made a silly typo.

Thanks so much!

Now, are we able to edit this formula, so it will leave the cell blank, if the customer name is blank?
I have many addresses/located that are not assigned to a customer.

Data would look like:

CustomerAB | 555 street | type1 | mixed
CustomerAB | 500 street | type2 | mixed
CustomerAB | 778 street | type1 | mixed
___________| 222 ave | type1 |
___________| 322 lane | type2 |
CustomerXZ | 111 street | type1 | not mixed
CustomerXZ | 089 street | type1 | not mixed
CustomerXZ | 019 street | type1 | not mixed

thanks for the fantastic help.

7. Like so

=IF(A2="","",IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed"))

8. Originally Posted by Bob Phillips
Like so

=IF(A2="","",IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed"))

This forum community is very helpful.

#### Posting Permissions

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