Hi there,
I am trying to create a formula to count distinct countries in the table below not including 'UK/US' or 'unclassified'. I can't figure it out. I have tried combining countif with an array formula but to no avail. So the answer For Group 1 would be 6 as there are 6 distinct countries not including UK/US & unclassified, the answer for group 2 would be 4.
Any help would be greatly appreciated.HTML Code:
Regards,
Forrestgump1980
Re: Can't count distinct excluding certain groups
is that what you want?
done with PivotTable and DataModel to get Distinct Count (PowerPivot add-in required)
Hi there,
The attached workbook contains a simpler, perhaps less powerful, method (e.g. it doesn't handle exclusions) but which might help to get you moving in the required direction.
Hope this helps - please let me know how you get on.
Regards,
Greg M
Try below array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Please Login or Register to view this content.
see the attached file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks