filter excel

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

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Filter in Excel

Filter in Excel

resolvedResolved · 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.

Please watch video below.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
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


Excel tip:

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

Server loaded in 0.06 secs.

✓ w3speedster