SUMIFS excluding hidden rows

[LouDetect]

Hi guys

Im completely new to this forum - anyone able to help me with formatting the following formula to exclude hidden row ? =SUMIFS(D18:D100000,N18:N100000,"ADWO")

Any help greatly appreciated

Thanks Lou

 

[igold]

Hi Lou,

Welcome to the MrExcel Forum.

Assuming the rows are hidden due to a filter, since you are using SUMIFS, how about adding a second Criteria Range and Criteria which would be equal to the Column and the Criteria that your sheet is filtered on.

This is obviously made up but something like this:

=SUMIFS(D18:D100000,N18:N100000,"ADWO", F18:F100000, "AAAA")

If the rows are hidden for some other purpose other than being filtered. I do not have an answer for you.

I hope this helps.

 

[Jeffrey Mahoney]

Here is a UDF that can be used with other functions. You give it the range you want to evaluate and it returns a range with cells that are not in hidden rows.

Public Function VisCells(R As Range) As Range
  Dim Cel As Range
  Dim u As Range
  
  For Each Cel In R
    If Cel.EntireRow.Hidden = False Then
      If Not u Is Nothing Then
        Set u = Union(u, Cel)
      Else
        Set u = Cel
      End If
    End If
  Next Cel
  If Not u Is Nothing Then Set VisCells = u
  
End Function

 

[nardagus]

Since you are using only one condition maybe you can use SUBTOTAL? It excludes hidden rows, so using autofilter you can hide all rows you don't need, and rest will be calculated...



[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]F1[/TH]
[TD="align: left"]=SUBTOTAL(9,D2:D7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]