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

 

[Patty3-4]

My mistake Fluff! I must've typed it wrong. I copied yours exact then added the IF statement as etaf suggested - it found them!! Thank you!! Could i leave the range open ended to an entire column instead of stating it will only by 100 rows?

 

[Patty3-4]

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’)

yes, i'll update my version now (Excel 2016), it's been a while since i've been on. Thank you very much for your help, both of you. This will help immensely!

 

[Fluff]

Could i leave the range open ended to an entire column

You could, but I would advise against as it will slow your workbook down. You would be better off using a realistic range that is larger than you data, but not by too much

 

[Patty3-4]

You could, but I would advise against as it will slow your workbook down. You would be better off using a realistic range that is larger than you data, but not by too much

sounds good, i'll do that. Thank you! so exciting!!! we get this question a lot - not about fruit lol - but how many are present with only "this" - oh! fantastic!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Patty3-4]

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

thank you for your help and for suggesting wrapping it in an IF statement ?