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:

[TABLE="width: 330"]
<tbody>[TR]
[TD="width: 110"]employee_id[/TD]
[TD="width: 110"]job_no[/TD]
[TD="width: 110"]union_id[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 330"]
<colgroup><col width="110" style="width:83pt"> <col width="110" style="width:83pt"> <col width="110" style="width:83pt"> </colgroup><tbody>[TR]
[TD="width: 110"]STE58[/TD]
[TD="width: 110"] 110080[/TD]
[TD="width: 110"]NYPLA[/TD]
[/TR]
[TR]
[TD]STE58[/TD]
[TD] 110080[/TD]
[TD]NYPLA[/TD]
[/TR]
[TR]
[TD]HAN56[/TD]
[TD] 110082[/TD]
[TD]NYPLA[/TD]
[/TR]
[TR]
[TD]STE58[/TD]
[TD] 110082[/TD]
[TD]NYPLA[/TD]
[/TR]
[TR]
[TD]JEF27[/TD]
[TD] 110082[/TD]
[TD]LITA[/TD]
[/TR]
[TR]
[TD]HAN56[/TD]
[TD] 110082[/TD]
[TD]NYPLA[/TD]
[/TR]
[TR]
[TD]HAN56[/TD]
[TD] 110082[/TD]
[TD]NYPLA[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 330"]
<tbody>[TR]
[TD="width: 110"]BRI80[/TD]
[TD="width: 110"] 110082[/TD]
[TD="width: 110"]NYTA[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 330"]
<colgroup><col width="110" style="width:83pt"> <col width="110" style="width:83pt"> <col width="110" style="width:83pt"> </colgroup><tbody>[TR]
[TD="width: 110"]STE35[/TD]
[TD="width: 110"] 120260[/TD]
[TD="width: 110"]LICA[/TD]
[/TR]
[TR]
[TD]SCH72[/TD]
[TD] 120260[/TD]
[TD]LICA[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 377"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] Job_no[/TD]
[TD] NYPLA[/TD]
[TD] LITA[/TD]
[TD] NYTA [/TD]
[TD] LICA[/TD]
[/TR]
[TR]
[TD="align: right"]110080[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]110082[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]120260[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

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:

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

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