Unique values with two criteria in one column

[leadsmith]

Hi,
I really need your help!
I'm trying to count the unique values in column A that meet two criteria that are in column K. I have over 673 employees that did a communication or support. I want a number that counts the employees that did a communication and support.

Column A- employee #
Column K- Communication or Support

Criteria 1: communication & criteria 2: support

Please help and thank you!

 

[oldbrewer]

name

comm

supp

name1

Y

name2

Y

name3

Y

name4

Y

Y

name5

name6

Y

name7

Y

name8

Y

name9

Y

Y

name10

4

name11

Y

name12

=SUMPRODUCT((B2:B21="Y")*(C2:C21="Y")*1)

name13

name14

Y

name15

Y

Y

name16

Y

name17

Y

name18

Y

name19

Y

name20

Y

Y

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[PCL]

that meet two criteria that are in column K

Can you give more details and example ...?

 

[leadsmith]

this is how I have it set up

A	B
employee#	Care
3816	support
3816	communication
3824	communication
3828	support
3830	support
3841	support
3890	support
3890	communication
3890	support

<tbody>
</tbody>

I want to count the unique number of employees that did a support and communication.

 

[PCL]

I do not see immediatly a formula to do the job so here an UDF
Assuming data from A1 to B10
Somewhere put =Counting(A2:B10)

Option Explicit


Function Counting(WkRg As Range) As Integer
Dim EmpDic   As Object
Set EmpDic = CreateObject("Scripting.Dictionary")
Dim EmNbpDic   As Object
Set EmNbpDic = CreateObject("Scripting.Dictionary")
Dim Rg   As Range
    With EmpDic
        For Each Rg In WkRg.Columns(1).Cells
            If (.exists(Rg.Value)) Then
                If (Rg(1, 2) <> .Item(Rg.Value)) Then
                    EmNbpDic.Item(Rg.Value) = Empty
                End If
            Else
                EmpDic.Item(Rg.Value) = Rg(1, 2).Value
            End If
        Next Rg
    End With
    Counting = EmNbpDic.Count
End Function

 

[oldbrewer]

sort your data into employee ascending then count the number of occasions when employee number equals the number above it

 

[Aladin Akyurek]

this is how I have it set up

A	B
employee#	Care
3816	support
3816	communication
3824	communication
3828	support
3830	support
3841	support
3890	support
3890	communication
3890	support

<tbody>
</tbody>

I want to count the unique number of employees that did a support and communication.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($B$2:$B$10,{"communication","support"},0)),$A$2:$A$10),$A$2:$A$10),1))

 

[oldbrewer]

employee#	Care
3816	support
3816	communication
3824	communication
3828	support
3830	support
3841	support
3890	support
3890	communication
3890	support
							Count of Care	Care
							employee#	communication	support	Grand Total
							3816	1	1	2
							3824	1		1
							3828		1	1
							3830		1	1
							3841		1	1
							3890	1	2	3
							Grand Total	3	6	9
								count grand totals > 1

<colgroup><col><col><col span="5"><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>