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 duplicat...
How about
=SUM(--(IF(SITES!B3:B13=B2,COUNTIFS(SITES!B3:B13,B2,SITES!C3:C13,SITES!C3:C13),0)>1))
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))- I sincerely appreciate your help. However, that didn't work. When I applied that on my spreadsheet, which should have 22 duplicates, the formula only comes back with 2. Thank you again for trying.