Multiple lookups query

Andrew Darley

Hi,

I've a sheet with various user names and 5 columns and trying to get the lookup to report on a user name and count up total of same instance in each of the columns -this can be 0-5 per row. In essence below is what I am trying to get to:

If user name = "Joe Bloggs" and column 1 = "abc" OR column 2="abc" OR column 3="abc" OR column 4="abc" OR column 5-"abc" this gives a result.

Any assistance much appreciated.

Thanks

A

Andrew Darley

Thanks Paul -works a treat!

Paul Newcome

You will need a separate COUNTIFS for each and then add them together.

=COUNTIFS([User Name]:[User Name], "Joe Bloggs", [Column1]:[Column1], "abc") + COUNTIFS([User Name]:[User Name], "Joe Bloggs", [Column2]:[Column2], "abc") + COUNTIFS([User Name]:[User Name], "Joe Bloggs", [Column3]:[Column3], "abc") + COUNTIFS([User Name]:[User Name], "Joe Bloggs", [Column4]:[Column4], "abc") + COUNTIFS([User Name]:[User Name], "Joe Bloggs", [Column5]:[Column5], "abc")

Andrew Darley

Thanks Paul -works a treat!

Paul Newcome

Happy to help. 👍️