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

HansV 462.6K Reputation points
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)

Was this answer helpful?

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!!!

    Was this answer helpful?

    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

    Was this answer helpful?

    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?

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    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.

    Was this answer helpful?

    0 comments