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

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, and the state in source column) count unique values from Raw data Column A, but do not count duplicate values.

Answer 1

Try using the following formula:

---

• Formula used in cell F2

=SUM(--(UNIQUE(FILTER($A$2:$A$38,
       ($C$2:$C$38>=F1)*
       ($C$2:$C$38<=F1+6)*
       ($D$2:$D$38="WA"),0))<>0))

---

Or, Using BYCOL()

=BYCOL(F1:M1,LAMBDA(α, 
  SUM(--(UNIQUE(FILTER(A2:A38,
  (C2:C38>=α)*
  (C2:C38<=α+6)*
  (D2:D38="WA"),0))<>0))))

---

One another alternative using COUNTIFS()

=SUM(--(COUNTIFS($A:$A,UNIQUE($A:$A),$C:$C,">="&F1,$C:$C,"<="&F1+6,$D:$D,"WA")>0))

---

You just need to add the Location criteria in the FILTER() function.