My formula is:
Formula:
Please Login or Register to view this content.
A2 has the name of the location, B2 a certain "level" type, FYI. I have another column in my table named "ID Number" - NOT in my formula yet - that is a 7 digit number for each record in the table... I need the formula to only count distinct values for that column... meaning if the number appears once, count it once, but if it appears twice or more, count it only once (assuming the other conditions are met).
Can you post a SMALL sample file and show us what result you expect?
A SMALL file will have about 20 rows worth of data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
In my sample, I need I COUNTIFS formula to count the number of East's with distinct IDs.... there are 6 East, but because ID 1234567 is repeated twice with an East, the formula should return 5.
I can add a helper column if it would actually help...
Sample 1.xlsx
Try this array formula**:
=SUM(IF(FREQUENCY(IF(Table1[Location]="East",Table1[ID]),Table1[ID]),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Awesome-sauce!!! Thank you so much!!
You're welcome. 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