Count Unique Records with Multiple Criteria

  • Hello,


    I need a count of unique records (exclude duplicates) based on multiple criteria.


    Issue at hand is that one Line can have multiple records per day per shift - we need to know how many days a Line, per shift, worked between two dates, not how many records there are.


    • Data is located on Sheet 'Data'
    • Output is located on Sheet 'Output'


    Below some examples and basic layout references.


    Thank you for your guidance,


    Stefan


    • Sheet 'Data' - A = Date
    • Sheet 'Data' - B = Shift
    • Sheet 'Data' - C = Line
    • Sheet 'Data' - D = Value - Note: ignore record in count altogether if value = 0, or value = ""


    Following example should return count = 10

    • 10 unique records on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""


    DateShiftLineValue
    04/01/201A1
    04/02/201A2
    04/03/201A3
    04/06/201A4
    04/07/201A5
    04/08/201A6
    04/09/201A7
    04/10/201A8
    04/15/201A9
    04/16/201A1




    Following example should return count = 5

    • 5 unique records on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""


    DateShiftLineValue
    04/01/201A1
    04/01/201A2
    04/02/201A3
    04/02/201A4
    04/03/201A5
    04/03/201A6
    04/06/201A7
    04/06/201A8
    04/07/201A9
    04/07/201A1


    Following example should return count = 1

    • 1 unique record on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""


    DateShiftLineValue
    04/01/203A0
    04/01/202B2
    04/02/201C0
    04/02/203A4
    04/03/202B0
    04/03/201C
    04/06/203A0
    04/06/202B8
    04/07/201C
    04/07/201A1



    • Sheet 'Output' - B1 = C_Date_Start (for example 04/01/20)
    • Sheet 'Output' - B2 = C_Date_End (for example 04/30/20
    • Sheet 'Output' - A4 - Line (for example A)
    • Sheet 'Output' - B3-D3 - Shift
    • Sheet 'Output' - B4 = C_Shift_1 - 1
    • Sheet 'Output' - C4 = C_Shift_2 - 2
    • Sheet 'Output' - D4 = C_Shift_3 - 3



    Date Start 04/01/20 . .
    Date End 04/30/20 . .
      Shift Shift Shift
    Line 1 2 3
    A . . .
    B . . .
    C . . .
    D . . .
    E . . .
    F . . .
    G . . .
    H . . .
    I . . .
    J . . .
    K . . .
  • Try this formula in Output sheet, B5:


    =SUMPRODUCT((Data!$A$2:$A$11>=$B$1)*(Data!$A$2:$A$11<=$B$2)*(Data!$B$2:$B$11=B$4)*(Data!$C$2:$C$11=$A5)*(Data!$D$2:$D$11<>0)/(COUNTIFS(Data!$A$2:$A$11,Data!$A$2:$A$11&"",Data!$B$2:$B$11,Data!$B$2:$B$11&"",Data!$C$2:$C$11,Data!$C$2:$C$11,Data!$D$2:$D$11,Data!$D$2:$D$11&"")))


    Copied down and across.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Thank you very much for taking the time looking at my problem.


    Example 1, and Example 3 provide the correct results, also when manipulating the data the results are as expected! - Excellent. - That is, unless I add duplicate dates.


    Example 2, does not produce the desired result. expected count is 5, whereas it returns 10.


    I attempted a 'Sumproduct' approach and never got anywhere near where you are. - Unfortunately I am still not wise enough to figure what and how to tweak to make it also work for instances such as in Example 2.


    I added my test file with the different scenarios in case it will help; sorry for not including it originally already.


    Stefan


    Record Count Eval Test.xlsx

  • Try:


    =SUMPRODUCT((Data!$A$1:$A$11>=$B$1)*(Data!$A$1:$A$11<=$B$2)*(Data!$B$1:$B$11=B$4)*(Data!$C$1:$C$11=$A5)*(Data!$D$1:$D$11<>0)/(COUNTIFS(Data!$A$1:$A$11,Data!$A$1:$A$11&"",Data!$B$1:$B$11,Data!$B$1:$B$11&"",Data!$C$1:$C$11,Data!$C$1:$C$11&"")))

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016