Find all that have only one type of value

[Patty3-4]

Good morning, I'm trying to write a countifs formula that will identify all IDNUM2 (I.D. numbers) that have a specific value, if another column has a specific value.

In the attached spreadsheet there are several IDNUM2's (columnB). I want to identify those that have ONLY watermelons (FRUIT columnC) in wood (TYPE columnD). In the example there is only 2 that meet the criteria. IDNUM2's could have numerous rows.

So far I get a circular reference warning. Any help would be greatly appreciated.

IDNUM1	IDNUM2	FRUIT	TYPE
592​	1K	WATERMELON	CARDBOARD
234​	5S	GUAVA	WOOD
234​	5S	PEACHES	CARDBOARD
234​	5S	APPLES	CARDBOARD
345​	6A	PEACHES	CARDBOARD
587​	3B	WATERMELON	WOOD
587​	3B	WATERMELON	WOOD
245​	9F	HONEYDEW	WOOD
245​	9F	JACKFRUIT	CARDBOARD
789​	7C	WATERMELON	WOOD
789​	7C	WATERMELON	CARDBOARD
801​	9E	BLUEBERRIES	CARDBOARD
801​	9E	WATERMELON	WOOD
402​	5A	WATERMELON	WOOD
604​	4P	WATERMELON	WOOD
604​	4P	JACKFRUIT	CARDBOARD

 

[etaf]

why only 2 meet criteria
is that 3B because they also match ?

Book4
	A	B	C	D
2	592	1K	WATERMELON	CARDBOARD
3	234	5S	GUAVA	WOOD
4	234	5S	PEACHES	CARDBOARD
5	234	5S	APPLES	CARDBOARD
6	345	6A	PEACHES	CARDBOARD
7	587	3B	WATERMELON	WOOD
8	587	3B	WATERMELON	WOOD
9	245	9F	HONEYDEW	WOOD
10	245	9F	JACKFRUIT	CARDBOARD
11	789	7C	WATERMELON	WOOD
12	789	7C	WATERMELON	CARDBOARD
13	801	9E	BLUEBERRIES	CARDBOARD
14	801	9E	WATERMELON	WOOD
15	402	5A	WATERMELON	WOOD
16	604	4P	WATERMELON	WOOD
17	604	4P	JACKFRUIT	CARDBOARD
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D17	Expression	=COUNTIFS($B$2:$B$17, $B2, $C$2:$C$17, "watermelon",$D$2:$D$17,"wood")>1	text	NO

 

[Patty3-4]

Yes, only 3B and 5A meet the criteria.

I'm looking to flag (maybe with a "1") all IDNUM2's that ONLY have watermelon and wood so I can pull them into a pivot table. Others may have watermelon and wood but if they have anything else, they're disqualified.

 

[etaf]

Not sure i understand why its only 3b and 5a ?
not 9e or 4p

you could add an IF into a new column , once the formula is as required
=IF ( COUNTIFS($B$2:$B$17, $B2, $C$2:$C$17, "watermelon",$D$2:$D$17,"wood")>1, 1,0)

 

[Patty3-4]

I can do it easily if there's only one row for a IDNUM2 with an IF AND formula, but not when they have multiple rows of data.
if(AND(B15<>B14,B15<>B16,C15="WATERMELON",D15="WOOD"),1,"")

 

[Fluff]

My take on this

+Fluff 1.xlsm
	A	B	C	D
1	IDNUM1	IDNUM2	FRUIT	TYPE
2	592	1K	WATERMELON	CARDBOARD
3	234	5S	GUAVA	WOOD
4	234	5S	PEACHES	CARDBOARD
5	234	5S	APPLES	CARDBOARD
6	345	6A	PEACHES	CARDBOARD
7	587	3B	WATERMELON	WOOD
8	587	3B	WATERMELON	WOOD
9	245	9F	HONEYDEW	WOOD
10	245	9F	JACKFRUIT	CARDBOARD
11	789	7C	WATERMELON	WOOD
12	789	7C	WATERMELON	CARDBOARD
13	801	9E	BLUEBERRIES	CARDBOARD
14	801	9E	WATERMELON	WOOD
15	402	5A	WATERMELON	WOOD
16	604	4P	WATERMELON	WOOD
17	604	4P	JACKFRUIT	CARDBOARD
18
19
Primary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D100	Expression	=SUMPRODUCT(($B$2:$B$100=$B2)*(($C$2:$C$100<>"Watermelon")+($D$2:$D$100<>"Wood")))=0	text	NO

 

[Patty3-4]

Not sure i understand why its only 3b and 5a ?
not 9e or 4p

you could add an IF into a new column , once the formula is as required
=IF ( COUNTIFS($B$2:$B$17, $B2, $C$2:$C$17, "watermelon",$D$2:$D$17,"wood")>1, 1,0)

9E also has blueberries and 4P has jackfruit. I tried the formula with one that should come back as 1 and it came back as 0.

 

[etaf]

Oh , i think i see now that Fluff has posted and your explanation, i see
Fluff has a solution , for conditional formatting , which could be added to an IF to have a column with a 1 in

 

[Patty3-4]

My take on this

+Fluff 1.xlsm
	A	B	C	D
1	IDNUM1	IDNUM2	FRUIT	TYPE
2	592	1K	WATERMELON	CARDBOARD
3	234	5S	GUAVA	WOOD
4	234	5S	PEACHES	CARDBOARD
5	234	5S	APPLES	CARDBOARD
6	345	6A	PEACHES	CARDBOARD
7	587	3B	WATERMELON	WOOD
8	587	3B	WATERMELON	WOOD
9	245	9F	HONEYDEW	WOOD
10	245	9F	JACKFRUIT	CARDBOARD
11	789	7C	WATERMELON	WOOD
12	789	7C	WATERMELON	CARDBOARD
13	801	9E	BLUEBERRIES	CARDBOARD
14	801	9E	WATERMELON	WOOD
15	402	5A	WATERMELON	WOOD
16	604	4P	WATERMELON	WOOD
17	604	4P	JACKFRUIT	CARDBOARD
18
19
Primary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D100	Expression	=SUMPRODUCT(($B$2:$B$100=$B2)*(($C$2:$C$100<>"Watermelon")+($D$2:$D$100<>"Wood")))=0	text	NO

Thank you, i plugged this in and the result for this formula comes back to "FALSE"

 

[Fluff]

That formula is for conditional formatting to highlight the rows that match your criteria.
If that is not what you want, then what do you want?

Also what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)