# Count Distinct Values OFFSET

1. ## Count Distinct Values OFFSET

I need to count the number of Distinct entries in two columns. (table is attached)

Column A has sorted account numbers. There are repeated entries.
Column B has a 'y' or an 'n'.

I need a formula to enter in my spreadsheet to keep the count up-to-date as entries are made.

The three separate formulas will be:
- Total DISTINCT Entries:
- Total DISTINCT Entries with an 'n':
- Total DISTINCT Entries with a 'y':

Obviously, for a value to be Distinct, it will only count the first instance of each instance of [Col-A and Col-B], meaning that at the most there could be one 'n' and one 'y' with each account number in Col-A.

The attachment shows the sample data and the desired results. I just need the formulas to put in E5,E7,E9 to do this for me, without having to manually create a filter every time I want an update.

Any assistance is greatly appreciated. I have found lots of similar problems but none that I could actually use to work this out. Thanks!

2. ## Re: Count Distinct Values OFFSET

A work in progress, but here's your Total Distinct formula:

=COUNT(1/FREQUENCY(IF(A1:A100&B1:B100<>"",MATCH(A1:A100&B1:B100,A1:A100&B1:B100,0)),ROW(A1:A100)-ROW(A1)+1))

This formula needs to be entered as an array using Ctrl+Shift+Enter. I totally got this from NBVC. https://www.excelforum.com/showthread.php?t=837655

I will continue testing out the others.

3. ## Re: Count Distinct Values OFFSET

And here's your distinct n:

Formula:
`Please Login or Register  to view this content.`

And distinct y:

Formula:
`Please Login or Register  to view this content.`

Both also entered as arrays using Ctrl+Shift+Enter

I found this one by Siddharth Rout

http://stackoverflow.com/questions/1...th-a-condition

4. ## Re: Count Distinct Values OFFSET

Thank you so much . . . this is great. I'm really gonna study it to figure out why I couldn't figure out how to do this.
One last thing - I searched for this before posting and couldn't find it - so obviously, in addition to my lacking Excel skills, my search skills need some work.
By what method did you search for and find this work by Siddharth Rout?

Again, thanks a million. I'll be glad when one day I know enough to contribute likewise.

5. ## Re: Count Distinct Values OFFSET

I think I landed on NBVC by Googling excel count unique combinations and I hit Siddhart Rout with excel count unique rows with a condition.

Sometimes the hardest part is asking the right question.

6. ## Re: Count Distinct Values OFFSET

Thanks for the help . . . I learn a lot from this forum.
You get a full 6-star rating! Have a great day!

7. ## Re: Count Distinct Values OFFSET

I learn at least as much as I try to teach.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1