I'd like to count the distinct number of colors given in two non-adjacent columns. In the attached example, the answer should be 7. It would need to skip blanks, and not be based on the unique function (in case a colleague using Excel 2016 has to use the workbook). I've been grappling with this since 11 am today and am out of ideas/have tried everything I could find. Anyone have a solid lead?
Thanks in advance.
J.
Last edited by glitzernes; 08-30-2022 at 11:12 AM.
How about:
=SUM(IF(Table1[Tom''s fav colors]="",0,1/COUNTIF(Table1[Tom''s fav colors],Table1[Tom''s fav colors])))+SUM(IF(Table1[Sal''s fav colors]="",0,IF(ISNUMBER(MATCH(Table1[Sal''s fav colors],Table1[Tom''s fav colors],0)),0,1)))
That did it - and I could follow the solution. Thanks Nick.
James
Last edited by AliGW; 08-31-2022 at 05:16 AM. Reason: SOLVED tag applied - no need to edit the thread title for this.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks