Forum Discussion
Re: SUMIF or Vlookup - count/sum the number of times based on unique values?
5 Replies
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 peopleFREQUENCY 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.
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
