Multiple entry formula

Hi,

I need some formula that determines whether a deal number is multi product using uses the below rules

(1) if a deal number is > 1 &
(2) if the products within those deal numbers are NOT unique

then "YES"
otherwise "NO"

I have attached an example spreadsheet

Many thanks
EE.xlsx
Seamus2626Asked:
Who is Participating?
 
Haris DulicCommented:
Here is the formula:

=+IF(B2>1;IF(COUNTIF(B:B;B2)>1;"YES";"NO");"NO")
0
 
Rgonzo1971Commented:
OR

=IF(AND(B2>1,COUNTIF(B:B,B2)>1),"YES","NO")

Regards
0
 
Seamus2626Author Commented:
But how is that formula taking into account the product data?

Your'e only looking at the deal number?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Seamus2626Author Commented:
So, if the deal number is not unique, and the the products are unique, thats a "NO", otherwise a "YES"
0
 
Rgonzo1971Commented:
Hi,

pls try

=IF(COUNTIFS(B:B,B2,D:D,D2)<>COUNTIFS(B:B,B2),"Yes","No")

Regards
0
 
Haris DulicCommented:
You are right ita not takng, you need to put D instead of B in the part related to countif
IF(B2>1;IF(COUNTIF(d:d;d2)>1;"YES";"NO");"NO")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.