Method to identify excluded values

Jeff Volkhammer
Jeff Volkhammer used Ask the Experts™
on
PET-ACCOUNTS.xlsx Greetings, Experts!
I have a question that I suspect will be fairly simple for you.  

I'm looking for a way to identify accounts where a value is NOT included.  The attached excel file includes a list of fictitious accounts and pets residing with each.  How can I use excel to understand which accounts do not have a value of 'HORSES'?  Obviously with a file this size I could eyeball it, but the 'real' file I'm attempting to work with has many thousands of rows.  Any help is appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You didn't attach your workbook. You may have forgotten to click 'Upload File' after choosing the file.
Jeff VolkhammerConsultant

Author

Commented:
Sorry, trying again.
If the count of the combination of 'Account' and 'HORSES' is 0, then that account does not have the Pet value 'HORSES'.
in attached column D are the list of account numbers, and in row 1 are the Pet types.

The formula in E2 is =COUNTIFS($A:$A,$D2,$B:$B,E$1)
Copied down and across.

The cells are marked yellow using conditional formatting when value is 0.
PET-ACCOUNTS.xlsx
Jeff VolkhammerConsultant

Author

Commented:
Thank you, Ejgil...this worked perfectly!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial