Forum Discussion
Summerize amount of re-occurent data
Hi all, I tried to figure it out myself... but just don't manage to find a working formula. I have a table with all our visited accounts this year. For every visit, there is a row in the tabl...
Dennis,
To be independent from the range size I'd convert it to table (Ctrl+T staying on any cell inside the range).
Power Pivot now is added to practically all SKUs, if you have it - on ribbon->Power Pivot->Add to Data Model staying on table.
Click Manage and here you may add two columns to table. First VisitsPerAccount
=CALCULATE(COUNT(Table1[Account Name]), ALLEXCEPT(Table1,Table1[Account Name]))
and second Visits
="Visit " & IF(Table1[VisitsPerAccount]>=4,"4+",Table1[VisitsPerAccount]) & " time" & IF(Table1[VisitsPerAccount]=1,"","s")
After that only create PivotTable based on data model table, result is like this
and attached