excel count total number

AI Training: For Busy Decision Makers & Professionals Book now

Forum 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

resolvedResolved · Medium Priority · Version 365

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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

View a unique list

You 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.

View all Excel hints and tips


Server loaded in 0.11 secs.