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.


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


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.

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

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.