#Excel: Building a Frequency Summary Table Based on an Excel List by David Hager

After publishing a tip on the most frequent item in a list

https://dhexcel1.wordpress.com/2017/05/09/excel-short-and-sweep-tip-11-most-frequent-item-in-column-by-david-hager/

I decided to build a model based on frequency.

The first formula used is the aforementioned most frequent item formila.

=INDEX(ListRange,MODE(MATCH(ListRange,ListRange,0)))

where ListRange = A2:A16

Then, another formula was needed to return subsequent frequent items,

=INDEX(ListRange,MODE(IF(COUNTIF($E$2:E2,ListRange)=0,MATCH(ListRange,ListRange,0))))

entered into cell E3 and copied down.

The formula to return the number for each item was entered in F2 and copied down.

=COUNTIF(ListRange,E2)

The amount spent on each transaction type was calculated the formula placed in G2 and copied down.

=SUM(IF(ListRange=E2,Total,0))

where Total = B2:B16

Finally, information can be used columns F & G to make a formula that will return the average for each transaction type.

=ROUND(G2/F2,2)

The finished table is shown in the following figure.

 

The frequency table file can be downloaded here.

FreqItems