sumif only for unhidden rows

[Bob Phillips]

As you want manually hidden as well as autofiltered, try this UDF

First add this UDF


Function IsVisible(ByVal Target As Range)
Dim oRow As Range
Dim i As Long
Dim ary()
ReDim ary(1 To 1, 1 To Target.Rows.Count)
i = 0
For Each oRow In Target.Rows
i = i + 1
ary(1, i) = Not oRow.EntireRow.Hidden
Next oRow
IsVisible = ary
End Function


Then use this formula

=SUM(TRANSPOSE(isvisible(C1:C20))*(C1:C20))

which is an array formula, so commit with Ctrl-Shift-Enter.

If the rows are manually hidden, hiding/unhiding does not trigger the UDF,
so you will need to F9.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

If you're using xl2003, you could use =subtotal(). It was enhanced to be able
to ignore those manually hidden rows, too.

 

[pwz]

How to build a conditional sum formula for unhidden rows only (other rows
are hidden manually or by auto-filter)? Thanks in advance!

Regards,
Pat

 

[Domenic]

Expanding on Dave's contribution and provided you have Excel 2003, since
you're looking for a conditional sum formula, you can use something like
the following formula for filtered data...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(CondRange,ROW(CondRange)-MIN(ROW(CondRange)
),0,1)),--(CondRange=Criteria),RangeToSum)

For manually hidden rows, change the 3 to 103.

Hope this helps!

 

[pwz]

Thanks Domenic! It works like a charm!

Expanding on Dave's contribution and provided you have Excel 2003, since
you're looking for a conditional sum formula, you can use something like
the following formula for filtered data...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(CondRange,ROW(CondRange)-MIN(ROW(CondRange)
),0,1)),--(CondRange=Criteria),RangeToSum)

For manually hidden rows, change the 3 to 103.

Hope this helps!

 

[pwz]

Thanks to Bob too!

As you want manually hidden as well as autofiltered, try this UDF

First add this UDF


Function IsVisible(ByVal Target As Range)
Dim oRow As Range
Dim i As Long
Dim ary()
ReDim ary(1 To 1, 1 To Target.Rows.Count)
i = 0
For Each oRow In Target.Rows
i = i + 1
ary(1, i) = Not oRow.EntireRow.Hidden
Next oRow
IsVisible = ary
End Function


Then use this formula

=SUM(TRANSPOSE(isvisible(C1:C20))*(C1:C20))

which is an array formula, so commit with Ctrl-Shift-Enter.

If the rows are manually hidden, hiding/unhiding does not trigger the UDF,
so you will need to F9.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)