Forum Discussion
Solved
Counting duplicates in a separate tab, filtering the adjacent column by the text in a cell
Good evening. I am using Office 365. Please see attached. I'm trying to do this:
Depending on the text in USE:B2, filter SITES: B:B by that text, then count cells in SITES: C:C which are duplicates.
I know how to count duplicates on a separate tab....
I know how to filter a column on a separate tab based on text in a cell....
But I don't know how to do both at the same time.
How about
=SUM(--(IF(SITES!B3:B13=B2,COUNTIFS(SITES!B3:B13,B2,SITES!C3:C13,SITES!C3:C13),0)>1))
8 Replies
Try this one. I think this is what you're looking to do:
=LET( sites, SITES!B3:B13, location, SITES!C3:C13, input, B2, filtered, FILTER(location, sites = input), bin, SORT(UNIQUE(filtered)), m, MAX(FREQUENCY(filtered, bin)), IF(m > 1, m, 0) )- Thank you. Would you mind putting it into the spreadsheet cell and attaching it to this thread? I tried and there is spacing that I don't know what to do with.
The previous snippet was from Advanced Formula Environment.
Here's from the formula bar:=LET(sites,SITES!B3:B13,location,SITES!C3:C13,input,B2,filtered,FILTER(location,sites=input),bin,SORT(UNIQUE(filtered)),m,MAX(FREQUENCY(filtered,bin)),IF(m>1,m,0))
=SUM((SITES!$B$3:$B$13=B2)*(SITES!$D$3:$D$13="Multiple Desk Setup"))
- Thank you for your help, but I am trying to count the duplicates in the C column, not the D column. It actually serves a different, and equally valid, purpose for me than counting the duplicate "Multiple Desk Setups" does.
How about
=SUM(--(IF(SITES!B3:B13=B2,COUNTIFS(SITES!B3:B13,B2,SITES!C3:C13,SITES!C3:C13),0)>1))
