Count unique value with date range and 1 criteria

0
votes

Can you please help me figure out the right formula for the below problem?

I want to count unique Item ID in Column A with a date range per agent.

You may find the link to the spreadsheet below:

https://docs.google.com/spreadsheets/d/1s-A3a-M5BlJI3xWNgeoL9ERKK056_0dsmSH7aae0TAY/

Thanks in advance for your help!

France

2
google-sheets
Hi pnuts, you may put the formula on column H. The answer should be match that of column F. – faital

2 Answers

1
votes

Based on your spreadsheet, you can nest a FILTER command inside of a COUNTUNIQUE function like so: =COUNTUNIQUE(FILTER($A$2:$A,$B$2:$B=E2,$C$2:$C>=date(2018,3,1),$C$2:$C<=date(2018,3,8)))

You will have to enter the dates by hand, but the $B$2:$B=E2 will reference the Agent name from the E column in your spreadsheet. All of the references with a $ in google are absolute, and will not change as you move the function around within the sheet.

0
votes

Option with QUERY for H2 and copied down to suit:

=count(query(A:C,"select count(A) where B='"&E2&"' and C>=date'2018-3-1' and C<=date'2018-3-8' group by A label count(A)''"))

Adjust the upper cutoff day to suit.