Calculate correct value based on multiple column criteria

[JKER]

Hello,

Below is a condensed table of an orderbook I am currently working on. Each row represents an order. An order consists of an assigned customer, the ordered item, and whether the customer requires special service (indicated by 50/100). The total orderbook contains multiple identical items, but being ordered by multiple customers.

Customer (column A)	Item (column B)	Required service (column C)	Output (column D)
Star	A22EM	100
Star	A22EM	50
G20 automation	A22IP	100
G20 automation	A22IP	50
G10 automation corp.	A22IP	100
YSAU	A66AA	50
YSAU	A77RT	100
Fest KG	A77RT	100
TR Robotics	A77RT	50
Pakvol GmbH	A80GM	50
Pakvol GmbH	A80GM	100
Pakvol GmbH	A80GM	50
TR Robotics	A80GM	100
Sau Corp.	A80GM	50
Sau Corp.	A80GM	50
Sau Corp.	A80GM	100
Pakvol GmbH	A80GM	100
RC Robotics	A48ZX	100
Int. Corp.	A33WB	50

For each item (row), I want to know whether there are more than one distinct customers requiring a '100' service. More specifically: column D should contain a value:
1; if there are more than one customers requiring a '100' service customer
0; if there is only one (or none) customer requiring a '100' service.

So, the following should be created (column D has been manually calculated):

Customer (column A)	Item (column B)	Required service (column C)	Output (column D)
Star	A22EM	100	0
Star	A22EM	50	0
G20 automation	A22IP	100	1
G20 automation	A22IP	50	1
G10 automation corp.	A22IP	100	1
YSAU	A66AA	50	0
YSAU	A77RT	100	1
Fest KG	A77RT	100	1
TR Robotics	A77RT	50	1
Pakvol GmbH	A80GM	100	1
Pakvol GmbH	A80GM	50	1
Pakvol GmbH	A80GM	50	1
TR Robotics	A80GM	100	1
Sau Corp.	A80GM	50	1
Sau Corp.	A80GM	50	1
Sau Corp.	A80GM	100	1
Pakvol GmbH	A80GM	50	1
RC Robotics	A48ZX	100	0
Int. Corp.	A33WB	50	0

I have tried to calculate column D with Vlookup, and index/match to check for each row whether there are items having >1 customers requiring a '100' service. Also, I tried to sum(countif(...) based on criteria. Unfortunately, I could not manage to create a correct formula. Could you help me out? Thank you.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1
2	Star	A22EM	100	0
3	Star	A22EM	50	0
4	G20 automation	A22IP	100	1
5	G20 automation	A22IP	50	1
6	G10 automation corp.	A22IP	100	1
7	YSAU	A66AA	50	0
8	YSAU	A77RT	100	1
9	Fest KG	A77RT	100	1
10	TR Robotics	A77RT	50	1
11	Pakvol GmbH	A80GM	50	1
12	Pakvol GmbH	A80GM	100	1
13	Pakvol GmbH	A80GM	50	1
14	TR Robotics	A80GM	100	1
15	Sau Corp.	A80GM	50	1
16	Sau Corp.	A80GM	50	1
17	Sau Corp.	A80GM	100	1
18	Pakvol GmbH	A80GM	100	1
19	RC Robotics	A48ZX	100	0
20	Int. Corp.	A33WB	50	0
Data

Cell Formulas
Range	Formula
D2:D20	D2	=SIGN(COUNTIFS(B:B,B2,A:A,"<>"&A2,C:C,100))

 

[JKER]

Dear Fluff,

Thank you for your response and the correct solution. You really helped me out!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JKER]

Dear Fluff,

Sorry to bother you. I think your suggested formula does not hold in all cases. I have added 4 more items to the list and the formula gives (Output):

Customer	Item	Required service	Output
TR Corp.	G50	50	1
TR Corp.	G50	50	1
AA	G50	100	0
AA	G50	50	0

However, the last column should be:

Customer	Item	Required service	Output
TR Corp.	G50	50	0
TR Corp.	G50	50	0
AA	G50	100	0
AA	G50	50	0

There are indeed 2 distinct customers ordering item 'G50', but only one order requires a '100' service. Therefore, the output should be '0' for all 'G50' items. The output should be '1' for all items, when there are >1 distinct customers requiring a '100' service. So, the output should be the following if only one distinct customer orders an item and requires a '100' service.

Customer	Item	Required service	Output
LT Corp.	60WT12	100	0
LT Corp.	60WT12	100	0

 

[Fluff]

Ok,how about

=--(COUNTIFS(B:B,B2,A:A,"<>"&A2,C:C,100)+COUNTIFS(B:B,B2,A:A,A2,C:C,100)>1)

 

[JKER]

I have the feeling your newly proposed Excel formula is closer to the final solution. However, your formula gives the following output:

Customer	Item	Required service	Output
TR Corp.	G50	50	1
TR Corp.	G50	50	1
AA	G50	100	1
AA	G50	100	1

It does indeed output '1' when 2 customers require a '100' service, but the condition should hold only for >1 distinct customers. In this case, the correct output should be:

Customer	Item	Required service	Output
TR Corp.	G50	50	0
TR Corp.	G50	50	0
AA	G50	100	0
AA	G50	100	0

In the table below, I changed the values in the 'Required service' column to show you a different scenario and its corresponding correct output.

Customer	Item	Required service	Output
TR Corp.	G50	50	1
TR Corp.	G50	100	1
AA	G50	50	1
AA	G50	100	1

Do you think an adaptation to your formula can solve this? Thanks in advance.

 

[Fluff]

Ok, how about

=--(IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$100,($B$2:$B$100=B2)*($C$2:$C$100=100)))),0)>1)

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Calculate correct value based on multiple column criteria
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[JKER]

Ok, how about

=--(IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$100,($B$2:$B$100=B2)*($C$2:$C$100=100)))),0)>1)

This did the trick. UNIQUE() was required to ensure the formula to work as intended. Thank you!