Counting Unique dates and names

Hi,

In the attached sheet I am trying to count how many unique days each individual has worked from some raw data which timestamps every field visit that they make.

I need a formula that will return the results shown in cells C3 and C4 of the 'Tracker' tab.

Any suggestions?

Try:

=SUMPRODUCT((Data!$B$2:$B$292=Tracker!B3)/COUNTIFS(Data!$A$2:$A$292,">="&INT(Data!$A$2:$A$292),Data!$A$2:$A$292,
"<"&INT(Data!$A$2:$A$292)+1,Data!$B$2:$B$292,Data!$B$2:$B$292))

I'm trying to pop this formula into my larger file (and both of these formulas work perfectly in the example file that I uploaded so thank you) but I'm running into some problems with it.

I made some minor alterations to it (see below) so it is referencing tabs with different names and some different cell ranges but when I update these it asks me to update the values for 'dump' and 'log'. This is what I have tried to use:

=SUMPRODUCT((Data Dump!$B$2:$B$150000=Call Log!C5)/COUNTIFS(Data Dump!$A$2:$A$150000,">="&INT(Data Dump!$A$2:$A$150000),Data Dump!$A$2:$A$150000,
"<"&INT(Data Dump!$A$2:$A$150000)+1,Data Dump!$B$2:$B$150000,Data Dump!$B$2:$B$150000))

The only things I've changed from Phuocam's formula is the name of the two tabs (Dump is now Data Dump and Log is now Call Log), the ranges from 292 to 150000 and the cell where the persons name is.

What am I doing wrong?

Originally Posted by nflsales

In your case may be the below

C3=SUM((FREQUENCY(IF(Data Dump!$B$2:$B$150000=$C5,INT(Data Dump!$A$2:$A$150000)),Data Dump!$A$2:$A$150000)>0)+0)

That worked (with the array) once I made another slight change to get rid of it asking for me to update the values:

=SUM((FREQUENCY(IF('Data Dump'!$B$2:$B$150000=$C5,INT('Data Dump'!$A$2:$A$150000)),'Data Dump'!$A$2:$A$150000)>0)+0)

Formula:

Please Login or Register  to view this content.

Thanks guys