Ad

Excel Count Unique Records That Match Criteria In Multiple Rows

- 1 answer

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.

MemberYearPaid StatusState
John2018PaidNY
John2019PaidNY
John2020PaidNY
John2021Not PaidNY
Mike2018PaidNY
Mike2019PaidNY
Mike2020PaidNY
Mike2021PaidNY
Mary2018PaidCA
Mary2019Not PaidCA
Mary2020PaidCA
Mary2021PaidCA
Jane2018Not PaidNY
Jane2019PaidNY
Jane2020Not PaidNY
Jane2021PaidNY
Tom2018PaidCA
Tom2019PaidCA
Tom2020Not PaidCA
Tom2021PaidCA
Ad

Answer

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)))

enter image description here

Ad
source: stackoverflow.com
Ad