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 |
CodePudding user response：
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)))
CodePudding user response：
You can add a new column Paid Code based on the Paid Status:
=IF([@[Paid Status]]="Paid";1;0)
Then build a pivot table using:
Memberas RawsYearas Columns- The Sum of the
Paid Codeas Values
Then you have a new table where you can easly extract the metrics you deisre.