• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Criteria

M

Mukhtar

New Member
Dear members,
I need your help to this question.

I have four cells A1 to A4.
Each cell contain 5 fruits, like apple, mango, orange, lemon & peach.
All the 4 cells contain any of the 5 fruits randomly.

My 3 favorite fruits are apple , mango & peach.
If I find all these 3 combinations in any of the four cell A1 to A4 then the answer is Yes or True.
If I find any 1 or 2 of these favorite fruits missing in any of these four cell but having something else from the list of fruits, then the answer is No or False.
If all the 3 favorite fruit is missing in cell A1 to A4 then again the answer is No or False.
If all the three combinations are present and also any of the four cells contain any of the 3 fruits in repetitive form then again the answer is Yes or True.

The main condition is If all my 3 favorite fruits combinations is there in 3 cells from A1 to A4 then the answer is Yes or True.

I tried the IF, IF + AND, IF + OR formula but still getting confused. Please help with this.
I do not know macros and VBA, so please give a normal Excel formula for this.

Thanks in Advance,
Regards,
Mukhtar
 
Hi Mukhtar,

I would suggest you to upload a sample file with your requirement in that with what should be expected result, which list is dynamic and which is static.

Regards,
 
Or else try below formula:

=SUMPRODUCT(--(COUNTIF(D1:D3,$A$1:$A$5)=1))=3

Here your fav. fruit are in D1:D3 and 5 fruits are in A1:A5, so if you list in A1:A5 contain your fav fruits only once this will result in True or else False.

Regards,
 
d
Somendra Misra said:
Or else try below formula:

=SUMPRODUCT(--(COUNTIF(D1:D3,$A$1:$A$5)=1))=3

Here your fav. fruit are in D1:D3 and 5 fruits are in A1:A5, so if you list in A1:A5 contain your fav fruits only once this will result in True or else False.

Regards,
Click to expand...

Dear Somendra Misraji,
Lots of thanks for responding back so fast,
Here I am uploading a sample excel file which I just prepared now, please have a look. I have checked your formula, it gives correct answer, but when one of the favorite fruit is repeated in any of the 4 cells along with the 3 favorite fruits then the answer goes FALSE no matter the 3 combinations are present and the answer should be TRUE.

Regards,
Mukhtar
 

Attachments

Last edited:
de
Somendra Misra said:
Hi,

In H29 Use below formula:

=SUMPRODUCT(--(COUNTIF(C29:F29,$D$3:$D$5)>=1))=3

Regards,
Click to expand...

Dear Somendra Misraji,

Lots, lots, lots, lots of Thanks to you, It worked perfectly. you are just great.
Lots of blessings to you. You reduced my stress. Again a lots of thanks to you.

Regards,
Mukhtar
 
You must log in or register to reply here.
Back
Top