Share via

Summing Up Specific Visible Cells / Conditional Subtotal

Anonymous
2011-04-04T05:36:47+00:00

Could there be written a formula so as to sum up only specific visible cells?

As per the sample below, where if I filter the first column as Old I want the sum of only Branch1, some type of array formula like =SUBTOTAL(9,IF(B2:B17=$B$2,C2:C17)):

Status Branch Amt
Old Branch1 1
New Branch1 2
Old Branch1 3
New Branch1 4
Old Branch1 5
New Branch2 6
Old Branch1 7
New Branch2 8
Old Branch2 9
New Branch2 10
Old Branch1 11
New Branch1 12
Old Branch2 13
New Branch1 14
Old Branch2 15
New Branch2 16

Thanx in advance

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-04-04T07:42:52+00:00

    I think you'd need to write a custom VBA function. Here is a very simple version, it could be improved by adding error checking etc.

    Function SumIfVisible(rng As Range, condition, rngSum As Range) As Double

      Dim i As Long

      For i = 1 To rngSum.Count

        If rng(i) = condition And rngSum(i).EntireRow.Hidden = False Then

          SumIfVisible = SumIfVisible + rngSum(i)

        End If

      Next i

    End Function

    Usage:

    =SumIfVisible(B2:B17,B2,C2:C17)

    2 people found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-04-05T06:45:20+00:00

    XClent Hans!!!

    0 comments
  2. Anonymous
    2011-04-04T07:32:04+00:00

    Hi Faraz,

    If I understand well, you'd like to put two criteria somewhare and make the calculation with those. I'd do that with helper columns. Something like this

    Kind regards

    Hans

    Old Branch1
    Status Branch Amt Crt1 Crt2 Criteria SUM =SUMIF(F3:F18;1;C3:C18)
    Old Branch1 1 =IF(A3=$D$1;1;0) =IF(B3=E1;1;0) =D3*E3
    New Branch1 2 =IF(A4=$D$1;1;0) =IF(B4=E2;1;0) =D4*E4
    Old Branch1 3 =IF(A5=$D$1;1;0) =IF(B5=E3;1;0) =D5*E5
    New Branch1 4 =IF(A6=$D$1;1;0) =IF(B6=E4;1;0) =D6*E6
    Old Branch1 5 =IF(A7=$D$1;1;0) =IF(B7=E5;1;0) =D7*E7
    New Branch2 6 =IF(A8=$D$1;1;0) =IF(B8=E6;1;0) =D8*E8
    Old Branch1 7 =IF(A9=$D$1;1;0) =IF(B9=E7;1;0) =D9*E9
    New Branch2 8 =IF(A10=$D$1;1;0) =IF(B10=E8;1;0) =D10*E10
    Old Branch2 9 =IF(A11=$D$1;1;0) =IF(B11=E9;1;0) =D11*E11
    New Branch2 10 =IF(A12=$D$1;1;0) =IF(B12=E10;1;0) =D12*E12
    Old Branch1 11 =IF(A13=$D$1;1;0) =IF(B13=E11;1;0) =D13*E13
    New Branch1 12 =IF(A14=$D$1;1;0) =IF(B14=E12;1;0) =D14*E14
    Old Branch2 13 =IF(A15=$D$1;1;0) =IF(B15=E13;1;0) =D15*E15
    New Branch1 14 =IF(A16=$D$1;1;0) =IF(B16=E14;1;0) =D16*E16
    Old Branch2 15 =IF(A17=$D$1;1;0) =IF(B17=E15;1;0) =D17*E17
    New Branch2 16 =IF(A18=$D$1;1;0) =IF(B18=E16;1;0) =D18*E18
    0 comments
  3. Anonymous
    2011-04-04T06:29:27+00:00

    Sorry buddy but none of your advices answer the question.

    How to calculate Specific & Visible cells only?

    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-04-04T06:10:35+00:00

    You could use

    =SUMIFS(C2:C17,A2:A17,"Old",B2:B17,"Branch1")

    or filter on both the Status and the Branch - SUBTOTAL will display the value that you want.

    0 comments