Excel Count unique records that match criteria in multiple rows

Question

In Excel I have a list of members by year and whether they paid or not in each year. I'm trying to determine the number of members who live in NY and have paid in BOTH 2019 and 2021. In the example below, only Mike and Jane meet the criteria. Because the "AND" condition is in the same column, I could not find a similar example. Thank you.

Member	Year	Paid Status	State
John	2018	Paid	NY
John	2019	Paid	NY
John	2020	Paid	NY
John	2021	Not Paid	NY
Mike	2018	Paid	NY
Mike	2019	Paid	NY
Mike	2020	Paid	NY
Mike	2021	Paid	NY
Mary	2018	Paid	CA
Mary	2019	Not Paid	CA
Mary	2020	Paid	CA
Mary	2021	Paid	CA
Jane	2018	Not Paid	NY
Jane	2019	Paid	NY
Jane	2020	Not Paid	NY
Jane	2021	Paid	NY
Tom	2018	Paid	CA
Tom	2019	Paid	CA
Tom	2020	Not Paid	CA
Tom	2021	Paid	CA

Answer 1

With the dynamic formula in Office 365 LET() and FILTER() We can return the correct value:

=LET(mem,A2:A21,yr,B2:B21,sts,C2:C21,st,D2:D21,fst,FILTER(mem,(yr=2021)*(sts="paid")*(st="NY")),scnd,FILTER(mem,(yr=2019)*(sts="paid")*(st="NY")),COUNT(MATCH(fst,scnd,0)))