+ Reply to Thread
Results 1 to 7 of 7

Formula that can check for two criteria per customer when multiple customer lines exist

  1. #1
    Registered User
    Join Date
    10-04-2016
    Location
    Dorset, England
    MS-Off Ver
    2010
    Posts
    2

    Formula that can check for two criteria per customer when multiple customer lines exist

    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?
    Attached Files Attached Files

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Formula that can check for two criteria per customer when multiple customer lines exis

    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



  3. #3
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Formula that can check for two criteria per customer when multiple customer lines exis

    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.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula that can check for two criteria per customer when multiple customer lines exis

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

  5. #5
    Registered User
    Join Date
    10-04-2016
    Location
    Dorset, England
    MS-Off Ver
    2010
    Posts
    2

    Re: Formula that can check for two criteria per customer when multiple customer lines exis

    Thank you for the swift replies. The Formula worked a treat

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Formula that can check for two criteria per customer when multiple customer lines exis

    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.

  7. #7
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Formula that can check for two criteria per customer when multiple customer lines exis

    Thanks for the Rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 11-28-2014, 07:53 AM
  2. [SOLVED] Products for specific customer should appear in text boxes when we choose that customer.
    By MariaPap in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-21-2014, 05:22 AM
  3. [SOLVED] Copying Customer Details Onto Each Sale Line Until Next Customer is Reached
    By Babybio77 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-22-2013, 11:33 AM
  4. Replies: 0
    Last Post: 10-25-2012, 09:52 AM
  5. Replies: 11
    Last Post: 10-25-2012, 12:26 AM
  6. sorting a list of customer invoices from columns into rows / customer
    By jr1984 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2011, 07:04 AM
  7. Replies: 1
    Last Post: 07-07-2006, 02:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1