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

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

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

• 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

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