Solved

Multiple entry formula

Posted on 2014-10-14
6
62 Views
Last Modified: 2014-10-14
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
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 250 total points
ID: 40379911
Here is the formula:

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

Expert Comment

by:Rgonzo1971
ID: 40379922
OR

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

Regards
0
 

Author Comment

by:Seamus2626
ID: 40379933
But how is that formula taking into account the product data?

Your'e only looking at the deal number?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Seamus2626
ID: 40379944
So, if the deal number is not unique, and the the products are unique, thats a "NO", otherwise a "YES"
0
 
LVL 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40379955
Hi,

pls try

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

Regards
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40379958
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question