3 Replies Latest reply: Nov 26, 2012 7:11 PM by pamela85
pamela85 Level 1 Level 1 (0 points)

Can numbers sum displayed values while excluding filtered/hidden values in a range?

  • 1. Re: Can numbers sum displayed values while excluding filtered/hidden values in a range?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Hi Pamela,

     

    The normal SUM function works on all the rows in a range, whether they are hidden or showing. However you can filter the "sum" by using SUMIF or SUMIFS with the same criteria as you used for showing rows.

     

    For instance, if you are showing all rows with a value greater than 5 in column A, =SUMIF(A, ">5") will sum column A of the showing rows. To sum column A's showing values where the filter criteria was that the value of column B is greater than 5, you would use: =SUMIFS(A, B, ">5")

     

    Jerry

  • 2. Re: Can numbers sum displayed values while excluding filtered/hidden values in a range?
    Barry Level 7 Level 7 (29,180 points)

    Hi Pamela,

     

    You can also use Jerry's suggestion for sums of cells in row, excluding hidden columns.

    In the example below, columns to be hidden were marked with an "a" in the header row, those to remain visible were marked with a B.

     

    Formula in A4: =SUMIF($1:$1,"b",$2:$2)

     

    The second table is a duplicate of the first, with columns B, D and F hidden. Note that the sum in A4 is unchanged.

    Picture 17.png

    The same set-up can be done, perhaps more efficiently, in the other direction.

    In the first table below The same table as above), Row 1 was blanked, and the resulting total in A4 became zero.

     

    The columns to be excluded from the sum were then hidden (Sum remained at zero).

     

    A "b" was typed into cell C1, the first of the unhidden cells to be summed, then Copied (command-C).

    With B1 still selected, press and hold Shift, and click on cell G1, the last visible cell in row 1, to extend the selection to that cell. Paste. The "b" will be pasted into the visible cells, and the sum in A4 will change to 6.

     

    The third table, expanded to show all columns, also shows that the pasted value ("b") was pasted only into the visible rows, and in hidden rows, row 1 remains blank.

    Picture 18.png

    Regards,

    Barry

  • 3. Re: Can numbers sum displayed values while excluding filtered/hidden values in a range?
    pamela85 Level 1 Level 1 (0 points)

    Thanks to both of you for your help, Jerrold and Barry, problem solved!

    Pam