Count Distinct Cells in Multiple Columns with Criteria

[leefletcher]

I'm trying to count how many distinct items appear across multiple columns with a single criteria from another column. My ability to use the right words might be keeping me from finding a solution on the web.

Cities	Restaurant 1	Unrelated information	Restaurant 3	Unrelated Information
Boston	McDonalds	Main	Popeyes	Down
Boston	Burger King	Elm	McDonalds	Up
Los Angeles	Wendys	North	Pizza Hut	West
Los Angeles	McDonalds	South	Wendys
Los Angeles	Pizza Hut	Oak

Desired result:

City	Total Restaurants	Distinct Restaurants
Boston	4	3
Los Angeles	5	3

How do I count the distinct entries across multiple columns (skipping over columns with unrelated information)?

 

[navic]

Maybe this idea helps you solve your task.
For total restaurants, place the following formula in cell 'H2' (copy down)

=COUNTIFS($A$2:$A$6,G2,$B$2:$B$6,"<>"&"")+COUNTIFS($A$2:$A$6,G2,$D$2:$D$6,"<>"&"")

For distinct restaurants, add 'helper columns', eg 'K' and 'L'. You can hide these columns or place them on another sheet.

Place the following formula in cell 'K2' (copy down)

=IFERROR(IFERROR(INDEX($A$2:$B$6,ROW($K2)-ROW($K$1),COLUMN($A$2:$A$6)),INDEX($A$2:$D$6,ROW($K2)-ROW($K$1)-ROWS($A$2:$D$6),COLUMN($A$2:$A$6))),"")

Place the following formula in cell 'L2' (copy down)

=IF(IFERROR(INDEX($B$2:$B$6,ROWS(C1:$C$1)),IFERROR(INDEX($D$2:$D$6,ROWS(C1:$C$1)-ROWS($B$2:$B$6)),""))=0,"",IFERROR(INDEX($B$2:$B$6,ROWS(C1:$C$1)),IFERROR(INDEX($D$2:$D$6,ROWS(C1:$C$1)-ROWS($B$2:$B$6)),"")))

For a distinct restaurant, place the following ARRAY or "CSE formula" in cell 'I2'. You need to finish the formula with Ctrl+Shift+Enter, not just Enter. (copy down)

=COUNT(1/FREQUENCY(IF($K$1:$K$20=$G2,IF($L$1:$L$20<>"",MATCH($L$1:$L$20,$L$1:$L$20,0))),ROW($L$1:$L$20)-ROW($L$1)+1))

I hope you can do it, see the screenshot below

 

[leefletcher]

Thank you. I hadn't thought of the helper column. I'll give it a shot. Thanks, again.