Forum Discussion
SUMIF or Vlookup - count/sum the number of times based on unique values?
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
Please upload the book and I'll have a look. Thanks.
Updated file.
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
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.
- 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
