Forum Discussion

ftb1ss's avatar
ftb1ss
Copper Contributor
Apr 02, 2021

Counting names

I am using a PC w/Windows 10 and MS 365.  I have a workbook with 12 sheets, one for each month of the year.  On each worksheet I have a list of all the people who have attended a meeting.  Each line ...
excel
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 04, 2021

ftb1ss 

I was puzzled by the fact that I got different results from a worksheet formula.

Turns out I placed my trust in firstName/lastName combinations rather than the NCRR number.  Perhaps Paul Hollandsworth is known as Curtis Lenderman to his friends?  My formula was

= LET(
  distinct, UNIQUE(VSTACK(March,April)),
  first,  INDEX(distinct,,2),
  last, INDEX(distinct,,1),
  Mch, COUNTIFS(March[Last], last, March[First], first),
  Apr, COUNTIFS(April[Last], last, April[First], first),
  Mch+Apr)

I would need to write a Lambda function to replace Charles Williams's VSTACK for the formula to post to other machines and the method does not scale well to incorporate additional tables.

Resources