Hi,
I am looking for a way to only filter out groups where all their records are null. For instance, if a group has 10 records, but only 5 entries in the name column, the group should not be counted. If a group has 10 records, but no entries in the name column, the group should be counted.
In the following example, we want to identify T350 and Cerbera only.
Any ideas?
Chimaera Chimaera Daniel Cerbera Tuscan Nate Tuscan Jade Tuscan Fox Tuscan Amaris Griffith Cody Griffith Joe Griffith T350 T350
How about
=ROWS(UNIQUE(FILTER(A2:A13,(COUNTIFS(A2:A13,A2:A13,B2:B13,"")=COUNTIFS(A2:A13,A2:A13)))))
Personally, I'd use Pivot Table.
Add first column as Row Label. Add Count of second column as Value field.
Then right click on Row labels. And Filter -> Value Filter -> Count of column 2 = 0.
Edit: If you need the other way around, just use filter for Count of column 2 greater than 0.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
Hi Fluff,
The count you supplied works perfectly - however, I should apologise because the original post wasn't clear, because what we want to do is return the group value rather than return a count total.
e.g.
Cerbera
T350
Rather than 2.
My apologies for the lack of clarity in the ask.
If you're looking for the number of groups that are blank, you should be able to use Fluff13's answer above. If you want the actual names, then just tweak his formula by removing the ROWS formula like this:
=UNIQUE(FILTER(A2:A13,(COUNTIFS(A2:A13,A2:A13,B2:B13,"")=COUNTIFS(A2:A13,A2:A13))))
Thanks Greg - home run!
Glad to help & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks