How to get unique count of values within a specific date range avoiding duplicate values

Question

I am attempting to obtain the sum of a column that has duplicate values, and have those duplicate values counted as 1 instance within a specific date range, that is representing the reporting week. So this count should show the count of unique values (kioskID) within that week beginning range.

I have the populate sheet with in E13 I need the count of the kioskID within the date range shown in E9. This count should exclude the duplicate counts.

I have tried countifs, sumifs, frequency and the return value is always 0 when that is not correct. I have tried to pivot the out but I want to avoid using a pivot table reducing manual input to the row in sheet 1.

Example of desired outcome: In week 14 (based on dates in raw data) count unique values from Raw data Column A, but do not count duplicate values.

enter image description here

Answer 1

Here is one way of doing this using SUMPRODUCT() or SUM() with a combination of UNIQUE()+FILTER() :

---

• Formula used in cell E2

=SUM(--(UNIQUE(FILTER($A$2:$A$25,($C$2:$C$25>=E1)*($C$2:$C$25<=E1+6),0))<>0))

---

The above formula needs to be filled right, however if you want to spill then using BYCOL()

=BYCOL(E1:L1,LAMBDA(α,SUM(--(UNIQUE(FILTER(A2:A25,(C2:C25>=α)*(C2:C25<=α+6),0))<>0))))

---

_{NOTE: You will need to change the cell range and reference as per your suit!}

---