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

[1boredguy]

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

[JoePublic]

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

[1boredguy]

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.

[JoePublic]

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

[Bob Phillips]

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

[1boredguy]

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.

[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"))

[1boredguy]

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"))

Thanks for the quick reply.

Your solution works great.

This forum community is very helpful.