Avoid Double Counting - Ignore Text in Criteria

Hi All,

I'm struggling again with the excel logic to perform something that feels simple, it's frustrating!

Ok here some sample data:

avoid double counting.jpg

I'm trying to create a formula that checks an ID and returns TRUE if a type other than "apple" or "banana" is there. The problem I'm having is double counting so if I write something like:

=IF(SUM(--(COUNTIFS($A$2:$A$15,C2,$B$2:$B$15,{"<>apple","<>banana"})>0))=2,TRUE," ")

it doesn't work because when counting excluding "apple" it counts "banana" and vice versa.

I want to do something with OR or AND, but that doesn't work or I can figure it out.

Help greatly appreciated!

Thank you

Try this...

Data Range

	A	B	C	D
1	ID	Type	ID
2	1	apple	1	TRUE
3	1	banana	2	TRUE
4	1	pear	3
5	2	apple	4	TRUE
6	2	mango	5
7	2	beetroot
8	3	apple
9	4	apple
10	4	banana
11	4	beetroot
12	5	apple
13	5	apple
14	5	banana
15	5	banana
16	------	------	------	------

This formula entered in D2 and copied down:

=IF(SUMPRODUCT(--(A$2:A$15=C2),--ISNA(MATCH(B$2:B$15,{"Apple","Banana"},0))),TRUE,"")

Thank you all very much, all your formulas work to the question I asked but I apologise because I've not asked the right question, I'm really sorry. My 5th post is correct, my first isn't - For a TRUE result I would like "apple" to be present + one other type that isn't "banana". Some examples:

TRUE: apple, banana, beetroot
TRUE: apple, apple, banana, pear
TRUE: apple, pear
FALSE/BLANK: apple
FALSE/BLANK: apple, banana
FALSE/BLANK: apple, apple, banana, banana
FALSE/BLANK: beetroot, pear
FALSE/BLANK: banana, beetroot

I'm sorry again. I will play around with the formulas you've created to see if I can get something to work.

Thank you

Exactly! and yes that works, thank you!!! of course! I was looking at trying to do something with AND but I hadn't considered putting a formula within it, I was just testing for specific text.

Aaargh! It so easy. I'm annoyed I couldn't think of that.

*Tweaking the formula slightly to make it work with the example in this thread:

=AND(COUNTIFS($A$2:$A$15,C2,$B$2:$B$15,"apple")>0,COUNTIFS($A$2:$A$15,C2,$B$2:$B$15,"<>apple",$B$2:$B$15,"<>banana")>0)

Thank you so much AGAIN!

I really hope to get better at these