Please see a simplified version of the base data I have and how it is set out versus the result I wish to achieve. The actual data is 3500 lines plus
I have used cars and fuel for illustration purposes only. In the first column I have Customer Name but this repeats with each vehicle they own. Each car is then petrol or diesel shown in the same column. I need to identify all the Customers who have both petrol and diesel cars.
Any suggestions?
There will me a (MUCH) simpler way of doing this:
=IF(E4="","",IF(IF(COUNTIFS($A$4:$A$18,E4,$C$4:$C$18,"Petrol")>0,1,0)+IF(COUNTIFS($A$4:$A$18,E4,$C$4:$C$18,"Diesel")>0,1,0)=1,"No","Yes"))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi -
Try this:
=IF(AND(SUMPRODUCT(($A$4:$A$18=E4)*($C$4:$C$18="PETROL")),SUMPRODUCT(($A$4:$A$18=E4)*($C$4:$C$18="DIESEL"))),"YES","NO")
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
Or
=IF(E4="","",IF(AND(COUNTIFS($A$4:$A$18,$E4,$C$4:$C$18,"Petrol"),COUNTIFS($A$4:$A$18,$E4,$C$4:$C$18,"Diesel")),"Yes","No"))
Thank you for the swift replies. The Formula worked a treat
Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
Last edited by Glenn Kennedy; 10-05-2016 at 09:31 AM.
Thanks for the Rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks