SUMIF or Vlookup - count/sum the number of times based on unique values?

[Bryan123]

I would like to create a formula that counts how many times a condition appears based dates.  For example in my example below, I want to the result to be 2 since Bob appears on separate dates 2

How many times does Bob appear on separate dates?

Sheet 1

Bob is in A2

Sheet 2, 

Bob is in A and the dates are in B

Bob  10/1/2019

Bob 10/1/2019

Bob 10/1/2019

Bob 10/5/2019

[#2]

@Bryan123 

Try this.

[#3]

Looks like the ranges have to be set which won't work for me as I'm always adding new items . Can A:A and B:B not work?

@Patrick2788 

[#4]

@Bryan123 

Entire column references are not recommended in arrays. It will tax Excel.

You can make the ranges longer than needed and it will work OK.

[#5]

@Patrick2788 

I changed it to like A2:A2000&B2:B2000 and it didn't seem to calculate correctly. 

=SUM(IF(FREQUENCY(MATCH(Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,0),MATCH(Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,0))>0,1))-COUNTIF(Completed!$A$2:$A$2000,"<>"&A2)

Bob is listed at A2.  Bob is also listed 3 times on the Completed worksheet but of those 3 times, there are only 2 different dates.

The results I'm getting is -1891

[#6]

@Bryan123 

Please upload the book and I'll have a look. Thanks.

[#7]

@Patrick2788 

See attached

[#8]

@Bryan123 

Try this one! I created a Pivot table with names in column A and dates in column B. I guess your real list will include more names than just Bob.

The Pivot table refers to a range A:B, so you can add entries without having to update the range. Just refresh the pivot table and add the persons name to be counted if he/she is new (in the area where i have the COUNTIF formulae. Note that you will get a (blank) (blank) in the pivot table. That's because you include all the blank rows in column A:B. These can be ignored.

I chose a "classic layout" to be able to repeat row headers, so that the name is repeated on every row. Then is becomes easy to count the occurrence for each name.

Hope this helps.

[#9]

This would be a shared wb and pivot tables don't refresh if it's shared. I may could unshare it when needed if that's the only option.

Thanks for the suggestion though

[#10]

@Bryan123 

Updated file.

[#11]

I'm afraid I left out some important info. Bob will not be the only person I'm looking for. Basically, this is to track how many times someone has been retrained.

Bob has made 8 errors and has been retrained twice on them on separate dates
Sue has made 15 errors and has been retrained 4 times on them on 4 separate dates
etc

I need to be able to drag the formula down and have it reference different people

[#12]

@Bryan123 

FREQUENCY can make this happen but the best solution at this point is the simplest.  Using Distinct Count in a pivot table.

To make Distinct Count available in the value field options the pivot source has to be tabled and added to the data model.

Sample attached.