Excel version of QlikView 'aggr' function

Question

I wonder if you can help me. I’m working with Excel 365 (with the Power BI plugins).

I have what seems to be a simple problem to solve but through all my googling I have not found an answer.

My outcome is to show how many times an ‘X’ has been to a given location.  

Location table (this is a pivot table)
Locations   Visits

X1      2
X2      2
X3      2
X4      2
X5      2
X6      2
X7      2
X8      2
X9      2
X10     2
X11     2
X12     2
X13     2
X14     2
X15     2
X16     2
X17     2
X18     2
X19     2
X20     2
X21     2
X22     2
X23     2
X24     2
X25     4
 

  I would like to turn the data in the ‘Location table’ into the ‘Times Visited’ table below  

Times Visited table
Times visited   count
2                 24
4                 1

  I created this table by counting how many times ‘2’ and ‘4’ appears in the ‘Location table’  

This shows that:
2 appears 24 times (i.e. 24 X’s have been to ‘Location 1’ two times)
4 appear once (i.e. 1 X has been to ‘Location 1’ four times)

  This is easily solved using QlikView but is proving difficult for me to do in excel 2013. In QlikView I can use a function called ‘aggr’.   Do you have any ideas how I can solve this in excel 2013?

Answer 1

In Excel you can use the countif method. This method takes two parameters, the range of values & the criteria. So if you set your range of values to be your 'Visits' & your criteria to be the visit number you should get the results you are looking for.

Answer 2

Since the raw data has already been pivoted merely copy the PT (reuse the existing cache for efficiency) and rearrange the fields: