excel get count of distinct names

Use pivot table, put names in filter and team in value. You will get results.

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

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

simple like this without use of control shift enter

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

Select all Open in new window

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 ]

Then it is not possible only with formula then it requires merging UDF with formula

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))

Select all 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

Select all Open in new window

list.xlsm

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))

Yes

please see attached.
list.xlsx

ok change this a2:a10 <> "" to a2:a10 <> "0"

if it does not work, can u please upload a example?

see attached example.
list.xlsx

Add this inside that formula right next to (A2:A10<>"")

Like this (A2:A10<>"")*(C2:C10<>"")*(B2:B10<>"")

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

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

neoptoent,

please find attached.  this version requires Control + Shift + Enter and it will take care of the empty cells too.
EE.xlsx