filter excel

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Filter in Excel

Resolved · Medium Priority · Version 365

Amber has attended:
Excel Intermediate course
Excel Intermediate course
Word Intermediate course

### Filter in Excel

Is there a way to take a sum of the numbers in a column when the filter is on?

I have a spreadsheet containing a set of numbers. There is one column that needs to be filtered, as there are 2 different types of data that need to be viewed separately. I need to know the sum of the numbers within these 2 types of data separately.

However, when I do a SUM calculation, the sum is taken from the entire column, which includes the numbers that have been filtered out.

The only way that seems to give me the correct number is if I drag to select all the cells and look at the sum at the very bottom of the screen. This seems like a long way around to do this, especially when there is a lot of data. Is there a better way?

Thanks for the help!

### RE: Filter in Excel

Hi Amber,

Thank you for the forum question.

If you only want to sum visible values, you will need the Subtotal function.

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:

 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.

### Selecting blank cells within a range of data

Select the range of data which includes the blank cells that you would like to select. Press the F5 key, this will take you to the GoTo dialogue box where you can click on Special and then select Blanks.

View all Excel hints and tips