Question:

In excel, is there a way to sum cells that are filtered only?

by  |  earlier

0 LIKES UnLike

I'm trying to sum rows that I have choose not to filter and the program it still adding all the row

 Tags:

   Report

2 ANSWERS


  1. In Excel 2002 onwards:

    If you use the SUBTOTAL function it will show the result of the filtered cells only.

    =SUBTOTAL(9,range)

    Range should include all cells before the filter is applied.

    The 9 gives you the sum, the other options include 1 for average, 2 for count numeric values, 3 for count all, 4 for Max, 5 for Min.


  2. Yes, there are two ways to do this (actually probably other ways too)

    1) the easiest is to use the built in sum command.

       Filter your cells

       Highlight the cells you'd like to sum

       On the bottom right on the long grey bar that says Ready, right click on the long rectangle.  (three spots to the left of NUM if numlock is on, or 2 to the left of CAPS, if caps lock is on)

       Select Sum (other commands are available too)

    2) A bit more complicated is the SUMIF command or using CSE commands.  If you use =SUMIF(), excel will help guide you through it.  If you want to use CSE, then go here http://www.mrexcel.com/tip031.shtml which explains CSE better than I ever could.

Question Stats

Latest activity: earlier.
This question has 2 answers.

BECOME A GUIDE

Share your knowledge and help people by answering questions.
Unanswered Questions