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

1. ## 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?

2. ## 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"))

3. ## 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")

4. ## 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. ## 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

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

Thanks for the Rep!

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

#### 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