# Count and tabulate number of unique values

Copper Contributor

# Count and tabulate number of unique values

Hi,

My starting point will be a column of annual payment amounts in a month and what I would like to do is calculate the number of monthly amounts and return them in a table.

I could /12 in a new column and then use countif but I will be working on several months at a time across multiple accounts so was wondering if there was a way I could select the data range and have excel spit out the results. To put it another way, what is the fastest way to get the table below with the least amount of input (bear in mind the monthly amounts will vary so right now I have to find out what monthly amounts occur before I can create the countif).

Many Thanks .

James

 Payment Frequency Amount C. Note Formula Formula Text Monthly 120.00 10 0 @ £5 Monthly 96.00 8 9 @ £8 Monthly 96.00 8 1 @ £10 Monthly 96.00 8 1 @ £20 Monthly 96.00 8 Monthly 240.00 20 Monthly 96.00 8 Monthly 96.00 8 Monthly 96.00 8 Monthly 96.00 8 Monthly 96.00 8 Formula Formula Text Monthly 60.00 5 4 @ £5 Monthly 96.00 8 11 @ £8 Monthly 96.00 8 2 @ £10 Monthly 96.00 8 Monthly 96.00 8 Monthly 60.00 5 Monthly 96.00 8 Monthly 96.00 8 Monthly 60.00 5 Monthly 96.00 8 Monthly 96.00 8 Monthly 96.00 8 Monthly 120.00 10 Monthly 96.00 8 Monthly 60.00 5 Monthly 120.00 10 Monthly 96.00 8
2 Replies

# Re: Count and tabulate number of unique values

If you have Microsoft 365 or Office 2021, you can use dynamic array formulas - see the attached demo workbook.

# Re: Count and tabulate number of unique values

Many Thanks for this Hans.

I will give it a try later and see how I get along.

Best Wishes

James.