1

I am trying to add column value in a sheet with a filter applied. The problem I have is that Excel is adding values for rows that have been filtered out rather than just those the filter is showing.

I am clicking on the first cell in the column and then doing, Ctrl + Shift + Down in order to select the values on screen, but this is returning the following formula.

=SUM(Q16:Q1532)

How can I apply this in order that I do not have irrelevant values as part of the range?

2 Answers 2

0

You can use the subtotal function. Looking at your question, I believe the formula would be =SUBTOTAL(9,Q1:Q1532). This will sum all filtered cells.

3
  • You mean only filtered? I want it to ignore those that are unfiltered
    – 3therk1ll
    Feb 3, 2016 at 14:43
  • haha - correct. Will update the answer
    – Chris
    Feb 3, 2016 at 14:45
  • No worries! Cheers
    – 3therk1ll
    Feb 3, 2016 at 14:50
0

I think Chris is correct though, if you want to sum all items that are shown on the worksheet after the filtering, you should be using the Subtotal function.

If you want to Sum the total of all the filtered out cells, you can use =Sum(Q16:Q1532)-Subtotal(9,Q16:Q1532)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .