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

1boredguy

New member
Joined
Feb 28, 2012
Messages
8
Reaction score
0
Points
0
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
 
For example:
=IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed")
 
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.
 
Can you post an actual workbook, please, because that formula should work?
 
It works fine for me, some mixed, some not mixed.
 
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.
 
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"))
 
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.
 
Back
Top