Google Sheets: COUNTIF in multiple rows but only ONCE if more than 1 in same column

Question

POSTERS NOTE: This is similar to a post by MultiformeIngegno in 12/2016, except I need the answer across columns, not rows.

ColA               ColB            ColC            ColD            ColE

ROLES              11/1/19         11/16/19        12/2/19         1/5/20
TOASTMASTER        SIMPSON         THOMPSON        SIMPSON         JONES
SPEAKER 1          JONES           JONES           TINTS           WISTLY
SPEAKER 2          TINTS           HUNT            HUNT            TINTS
TABLE TOPICS       WISTLY          HUNT            SIMPSON         HUNT
EVAL 1             SIMPSON         SIMPSON         WISTLY          JONES

I'm creating an attendance tracker, but attendees can take on several roles during the same day, so I can't just perform a COUNTIF or I'll get extra counts/day. What formula can I use to count the occurrences of an attendee (let's say Simpson) in ColB:ColE but only counting it ONCE if more than an occurrence appear in the same column (day)? In this case for example, Simpson should have a full attendance count of 3 for the four-meeting period, despite performing 5 roles.

Answer 1

It is almost the same. Here is the formula:

=ArrayFormula(COUNTIF(MMULT(SEQUENCE(1, ROWS(B2:E6), 1, 0), --(B2:E6="SIMPSON")), ">0"))

Here the sum done bu MMULT is column wise, there it was row wise. Hence the difference.

Answer you've mentioned: link.