excel get count of distinct names

Hi,
I am trying to find an excel formula to get the count of distinct names in column A based on the number in column b

so in this table, I would want the distinct number of names in column A where the value in column B is 2 (so in this example the count would be 3... jane, bob and mary)

I cant figure out this formula

Please help
note:
The list is dynamic so I need to use the whole column for ranges
list.xlsx
neoptoentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Amit KumarCommented:
Use pivot table, put names in filter and team in value. You will get results.
0
FlysterCommented:
If you want to use a formula, you can try this:

=SUM((B2:B9=2)/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9))

This is an array formula, so you enter it into your cell select ctrl+shift+enter.

Flyster
0
zorvek (Kevin Jones)ConsultantCommented:
Use this formula:

=SUM(IF(FREQUENCY(IF((LEN(A2:A100)>0)*(B2:B100=1),MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),1))

Change the bottom row to include the maximum number of possible rows.

Enter the formula by pressing CTRL+SHIFT+ENTER.

The formula handles blank rows so you don't need to worry about the bottom row in the cell references. Just make it low enough to handle all possible scenarios.

Kevin
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ProfessorJimJamCommented:
simple like this without use of control shift enter

=SUMPRODUCT((B2:B9=2)*(A2:A9<>"")/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9))

Open in new window

0
neoptoentAuthor Commented:
Hi
Thanks
I have the excel sheet as a named table
Would I be able to use the table name as a reference instead of a range

Table name: cname
0
ProfessorJimJamCommented:
Yes,

Just click on the b2:b9 then erase it with back space then with mouse select the column of the table then the table name with its column will be automatically put inside formula. You don't have to type the table name and column name there. If you have to then put the table name then [ column name ]
0
neoptoentAuthor Commented:
Thanks

If I had a thirds column and wanted to do the counting based on the value 2 in column b as well as the value of "yellow" in column c

So count all distinction values in column A where column b is 2 and column c is yellow

Possible?
0
ProfessorJimJamCommented:
Then it is not possible only with formula then it requires merging UDF with formula
0
neoptoentAuthor Commented:
Not sure what that means... Can I use a formula ?
0
ProfessorJimJamCommented:
that means that inside formula you could use the UDF.

see attached file.  it now works with condition of yellow cells as well.

the below code is inserted in module of attached workbook which create the defined function within the formula

=SUM((B2:B10=2)*(A2:A10<>"")*(IF(GetCellColor(A2:A10)=65535,1,0))/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))

Open in new window


as you can see the new argument criteria if GetCellColor of the range equals to 65535 which means yellow then true otherwise false.  

remember the formula is array formula and requires the special keystroke of Control Shift and Enter to enter.

Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function

Open in new window

list.xlsm
0
neoptoentAuthor Commented:
Just to make sure. The actual cell color won't change, the word yellow will appear in the cell
0
ProfessorJimJamCommented:
In this case it is even easier. No need for any UDF.

Assuming the column to check the value yellow is range c2:c10 then =SUM((B2:B10=2)*(A2:A10<>"")*(C2:C10="yellow")/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10,C2:C10,C2:C10))
0
neoptoentAuthor Commented:
And this will give me the unique values in a
0
ProfessorJimJamCommented:
Yes
0
neoptoentAuthor Commented:
I am getting a #div/0 error
0
ProfessorJimJamCommented:
please see attached.
list.xlsx
0
neoptoentAuthor Commented:
In a sheet I am working with the a2:a10 <> "" is equal to 0
So that is causing the error

How can I fix that
0
ProfessorJimJamCommented:
ok change this a2:a10 <> "" to a2:a10 <> "0"

if it does not work, can u please upload a example?
0
neoptoentAuthor Commented:
ok the issue is when the column has blanks in the rows

If there a way to ignore the empty cells
0
ProfessorJimJamCommented:
see attached example.
list.xlsx
0
neoptoentAuthor Commented:
if column b or c have a blank cell then I get the same eror of div0.
Is there a way to ignore blanks in column B and C.
0
ProfessorJimJamCommented:
Add this inside that formula right next to (A2:A10<>"")

Like this (A2:A10<>"")*(C2:C10<>"")*(B2:B10<>"")
0
zorvek (Kevin Jones)ConsultantCommented:
Y'all seem to be having a lot of fun with this.

I'll repeat my formula above as it had already solved the original question and, with the addition of two more conditions ("0" in column A and "Yellow" in column C), still solves the problem with any number of data rows:

=SUM(IF(FREQUENCY(IF((LEN(A2:A100)>0)*(A2:A100<>0)*(B2:B100=2)*(C2:C100="Yellow"),MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),1))

Enter the formula by pressing CTRL+SHIFT+ENTER.

Kevin
0
ProfessorJimJamCommented:
zorvek,  

yes, really had fun :-)


without control shift enter ;-)

=SUMPRODUCT((B2:B13=2)*(A2:A13<>0)*(C2:C13="Yellow")*(A2:A13<>"")*(B2:B13<>"")*(C2:C13<>"")/COUNTIFS(A2:A13,A2:A13,B2:B13,B2:B13,C2:C13,C2:C13))


with attachment
list.xlsx
0
neoptoentAuthor Commented:
thanks

if you delete the contents of c10 so the cell is empty the formula shows a div/0
can if ignore the empty cells in columns b &C?
0
ProfessorJimJamCommented:
neoptoent,

please find attached.  this version requires Control + Shift + Enter and it will take care of the empty cells too.
EE.xlsx
0
neoptoentAuthor Commented:
Hi
Can you please copy the formula into the thread, can't open the file
0
ProfessorJimJamCommented:
=SUM(IF((B2:B13=2)*(A2:A13<>0)*(C2:C13="Yellow")*(A2:A13<>"")*(B2:B13<>"")*(C2:C13<>""),1/COUNTIFS(A2:A13,A2:A13,B2:B13,B2:B13,C2:C13,C2:C13),0))

Open in new window


with Control Shift Enter
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
neoptoentAuthor Commented:
Great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.