98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Excel count total number of filled rows without counting duplica
Excel count total number of filled rows without counting duplica
Resolved · Medium Priority · Version 365
George has attended:
Excel Intermediate course
PowerPoint Intermediate Advanced course
Virtual training delivery skills course
Excel Advanced course
Excel Advanced course
Effective Communication Skills course
Time Management course
Excel count total number of filled rows without counting duplica
I would like to know how many clients I have. Each client’s details are set out on a different row. The spreadsheet has multiple months and years set out chronologically from top to bottom. I update each client’s case if need be each month. Not every client features in each month.
Is there a simple way to add up how many clients I have which discounts repetition of the same client further down the spreadsheet? I only want to count each one once but I want to a total of every one that features at least once.
RE: Excel count total number of filled rows without counting dup
Hi George,
Thank you for the forum question.
I have got the information that you have Excel 365. If your version is 365, you have the Unique function.
The Unique function together with the Counta function can do what you want. If you have all the client names in the range A2:A500 type:
=Counta(Unique(A2:A500))
Please let me know, if you do not have Excel 365 or you cannot get this to work.
You can also copy the whole column of client names and paste the names in another column and remove duplicates (you have the remove duplicates tool on the Data tab in the Data Tools group). Then you will have a column with unique names only. You can then use the Counta function to count the range with unique client names.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:View a unique listYou have a column with hundreds of entries, and you need to see what unique items are entered in it. Select any cell in that column, hold down Alt and press the down arrow: Excel produces an alphabetically-sorted list of unique entries in that column. |