Counting Unique values using three sets of criteria

[rvames]

Im having trouble figuring out how to count unique values using three sets of criteria. for example:

employee_id

job_no

union_id

<tbody>
</tbody>

STE58	110080	NYPLA
STE58	110080	NYPLA
HAN56	110082	NYPLA
STE58	110082	NYPLA
JEF27	110082	LITA
HAN56	110082	NYPLA
HAN56	110082	NYPLA

<colgroup><col width="110" style="width:83pt"> <col width="110" style="width:83pt"> <col width="110" style="width:83pt"> </colgroup><tbody>
</tbody>

BRI80

110082

NYTA

<tbody>
</tbody>

STE35	120260	LICA
SCH72	120260	LICA

<colgroup><col width="110" style="width:83pt"> <col width="110" style="width:83pt"> <col width="110" style="width:83pt"> </colgroup><tbody>
</tbody>

All the above data is on sheet one and I want to display the results on sheet 2. I need to know how many unique row records there are for each job_no.

Job_no	NYPLA	LITA	NYTA	LICA
110080	1	0	0	0
110082	2	1	1	0
120260	0	0	0	1

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I would like to have the table look like this when it is all done. Any help would be greatly appreciated.

 

[circledchicken]

Hi and welcome to the forum,

Assuming the last value in the table is a typo (i.e. 120260 / LICA should be 2 not 1), then perhaps try something like this on Sheet2 and adjust the ranges as appropriate:

=SUM(
    IF(
      FREQUENCY(
        IF(Sheet1!$B$2:$B$20 = $A2,
        IF(Sheet1!$C$2:$C$20 = B$1,
        IF(Sheet1!$A$2:$A$20 <> "",
          MATCH("~" & Sheet1!$A$2:$A$20, Sheet1!$A$2:$A$20 & "", 0)))),
        ROW(Sheet1!$A$2:$A$20) - ROW(Sheet1!$A$2) + 1),
      1))

If you have Excel 2013, you can do this directly in a PivotTable instead - see for example: Distinct Count in Pivot Tables
For earlier versions, a PivotTable approach is still possible (and maybe preferable) using a workaround like this for example: Unique Count in Excel Pivot Table With PowerPivot

 

[WarPigl3t]

You said 3 criteria but the lookup is only 2 criteria from what I can tell in the output sheet. Do you know how to use VBA? Here's a function for you.

Function myFunction(Criteria1, Criteria2, TheRange)
    'B2 = myFunction($A2, B$1, Sheet1!$B$2:$C$10) 
    Application.Volatile
    currentCol = "B"
    For each cell in TheRange
        If currentCol = "B" Then
            If Criteria1 = cell Then
                output = TRUE
            End If
            currentCol = "C"
            Next cell
        End If
        If currentCol = "C" Then
            If Criteria2 = cell AND output = TRUE Then
                myCounter = myCounter + 1
            End If
            currentCol = "B"
            output = FALSE
        End If
    Next cell
    myFunction = myCounter
End Function

 

[rvames]

Thank you WarPiglet but I do not know how to use VBA.

CircledChicken that formula worked but I have a quick question:

Job Name	Job#	PM	Carpenter	Taper	Laborer
AMC 13 BAY PLAZA THEATER	140260	JM	3	4	0
COLUMBIA MANHATTANVILLE	130170	MC	30	2	1
CUMC MRI INSTALLATION	140570	MI	3	0	0
MARY MANNING WALSH NURSING	140110	RD	3	0	0
MMA FIRE ALARM SMOKE PROTECT	132390	TB	4	0	0
MSKCC MRI SUITE	140020	RO	4	0	0
NYPH ADULT ED PHASES 3-6	144040	MI	7	0	0
NYPH CHONY BUNDLE INFRASTRUC	144080	MI	1	1	0
NYPH CHONY CENTRAL 11 PICU	144030	MI	6	2	0
NYPH EMERGENCY GENERATOR	144070	JM	1	0	0
NYPH GP4 DONOR WALL	144100	MI	3	0	0
NYPH HARKNESS AUTOMATED LABS	144000	RO	9	1	0
NYPH MILSTEIN 9 GARDEN SOUTH	144120	RO	2	0	0
NYULMC TISCH MRI SUITE	140150	JC	4	2	0
PS 339 QUEENS	140500	KD	8	0	0
PS 340 MANHATTAN	130020	MI	1	0	0
PS 62R STATEN ISLAND	130010	JC	21	7	0
SNCH F WING ELEV & LIFE SAFETY	140620	JM	1	0	0
SNCH F1 & F2 TRANS CARE UNITS	140010	JM	2	1	0
WAREHOUSE JOB	WAREHOUSE		4	0	0
WCMC 3rd FLOOR IMAGING SUITE	140220	JM	6	0	0

<tbody>
</tbody>

I used the formula you gave me to come up with this table.

=SUM(IF(FREQUENCY(IF('Payroll Data'!$F$2:$F$1306=$B2,IF('Payroll Data'!$T$2:$T$1306="LICA",IF('Payroll Data'!$D$2:$D$1306<>"",MATCH("~"&'Payroll Data'!$D$2:$D$1306,'Payroll Data'!$D$2:$D$1306&"",0)))),ROW('Payroll Data'!$D$2:$D$1306)-ROW('Payroll Data'!$D$2)+1),1))+SUM(IF(FREQUENCY(IF('Payroll Data'!$F$2:$F$1306=$B2,IF('Payroll Data'!$T$2:$T$1306="NYCA",IF('Payroll Data'!$D$2:$D$1306<>"",MATCH("~"&'Payroll Data'!$D$2:$D$1306,'Payroll Data'!$D$2:$D$1306&"",0)))),ROW('Payroll Data'!$D$2:$D$1306)-ROW('Payroll Data'!$D$2)+1),1))+SUM(IF(FREQUENCY(IF('Payroll Data'!$F$2:$F$1306=$B2,IF('Payroll Data'!$T$2:$T$1306="NYPLA",IF('Payroll Data'!$D$2:$D$1306<>"",MATCH("~"&'Payroll Data'!$D$2:$D$1306,'Payroll Data'!$D$2:$D$1306&"",0)))),ROW('Payroll Data'!$D$2:$D$1306)-ROW('Payroll Data'!$D$2)+1),1))+SUM(IF(FREQUENCY(IF('Payroll Data'!$F$2:$F$1306=$B2,IF('Payroll Data'!$T$2:$T$1306="RCKCA",IF('Payroll Data'!$D$2:$D$1306<>"",MATCH("~"&'Payroll Data'!$D$2:$D$1306,'Payroll Data'!$D$2:$D$1306&"",0)))),ROW('Payroll Data'!$D$2:$D$1306)-ROW('Payroll Data'!$D$2)+1),1))+SUM(IF(FREQUENCY(IF('Payroll Data'!$F$2:$F$1306=$B2,IF('Payroll Data'!$T$2:$T$1306="WSTCA",IF('Payroll Data'!$D$2:$D$1306<>"",MATCH("~"&'Payroll Data'!$D$2:$D$1306,'Payroll Data'!$D$2:$D$1306&"",0)))),ROW('Payroll Data'!$D$2:$D$1306)-ROW('Payroll Data'!$D$2)+1),1))

Is this the best way to use this formula or is there a way to consolidate this? Also I am not very familiar with pivot tables.

 

[circledchicken]

Hi,

I'm not sure what you mean. The table you've posted is quite different to the example you provided initially.

Here are a few useful resources for learning more about PivotTables:
Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
http://www.contextures.com/xlPivot01.html#Start
Overview of PivotTable and PivotChart reports - Excel
Using Pivot Tables in Microsoft Excel - Peltier Tech Blog