CODEWITHSUNDEEP
excelunique
Jeff
Jeff

Reputation: 3

Excel Count unique records that match criteria in multiple rows

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

Upvotes: 0

Views: 96

Answers (3)

Solar Mike
Solar Mike

Reputation: 8415

Well some commenters suggested that it might not work, so here is my offering, where cell I1 has the result:

enter image description here

In cell G2 (count for one year):

COUNTIFS(A2:A21,F2,D2:D21,F$8,C2:C21,G$8,B2:B21,H$8)

In cell H2 (count for second year):

COUNTIFS(A2:A21,F2,D2:D21,F$8,C2:C21,G$8,B2:B21,I$8)

In cell I2 (and to make sure both are present):

IF(AND(G2,H2),1,0)

The list of names in column F could be generated by the use of unique().

The data in F8:I8 could be with data validation to get different results as needed.

I always think several small functions are easier to control than one longer function...

And the table G2:H6 can be reduced to 4 cells with:

IF(AND(COUNTIFS(A2:A21,F2,D2:D21,F$8,C2:C21,G$8,B2:B21,H$8),COUNTIFS(A2:A21,F2,D2:D21,F$8,C2:C21,G$8,B2:B21,I$8)),1,"")

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152650

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

Upvotes: 1

ifcz
ifcz

Reputation: 11

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:

  • Member as Raws
  • Year as Columns
  • The Sum of the Paid Code as Values

Then you have a new table where you can easly extract the metrics you deisre.

Upvotes: 0

Related Questions