Forum Discussion
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
If you have Microsoft 365 or Office 2021, you can use dynamic array formulas - see the attached demo workbook.
- Many Thanks for this Hans.
I will give it a try later and see how I get along.
Best Wishes
James.
