# 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.

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

 Date Shift Line Value 04/01/20 1 A 1 04/02/20 1 A 2 04/03/20 1 A 3 04/06/20 1 A 4 04/07/20 1 A 5 04/08/20 1 A 6 04/09/20 1 A 7 04/10/20 1 A 8 04/15/20 1 A 9 04/16/20 1 A 1

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

 Date Shift Line Value 04/01/20 1 A 1 04/01/20 1 A 2 04/02/20 1 A 3 04/02/20 1 A 4 04/03/20 1 A 5 04/03/20 1 A 6 04/06/20 1 A 7 04/06/20 1 A 8 04/07/20 1 A 9 04/07/20 1 A 1

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

 Date Shift Line Value 04/01/20 3 A 0 04/01/20 2 B 2 04/02/20 1 C 0 04/02/20 3 A 4 04/03/20 2 B 0 04/03/20 1 C 04/06/20 3 A 0 04/06/20 2 B 8 04/07/20 1 C 04/07/20 1 A 1

• 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!

• 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!