Home

Summerize amount of re-occurent data

Dennis Depoorter
Occasional Contributor

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 table.

Now I have to make a summary table that tells me how many accounts that are in the list that has been visited 1 time, 2 times, 3 times, 4 times and more than 4 times.

 

Attached a example file of the table.

 

Whats important:

  • the formula should be as fast as possible because it's for a very large data file
  • the column reference should be of the following type: A:A because I need to update on a weekly base the data. I don't want to change every week the formula.

Thanks all!!! Hope someone can help me out here.

 
1 Reply

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

image.png

and attached

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies