title: edit the program to sumif with visible cells in MS excel
body: <p>i got some countifv thats counts visible and sumifsv which sum visible cells only ... i want sumif only visible using vba</p>
  
  <p>Default Using SUMIFS on visible lines only</p>
  
  <p>I have a large table, lots of rows and lots of columns that has material (Sand, stone, etc.) batched information. I wanted to be able to allow the user to use filters to select which data they want to view, then be able to review some summary information (totals by hour of the day, totals by material, etc.) on other sheets.</p>
  
  <p>I had it working really well and fast using DSUM and/or SUBTOTAL, but these functions don't exclude non-visible (Filtered) lines in a list.</p>
  
  <p>Using some previous posts I saw on this site, I was able to come up with something that works, but it is extremely slow.</p>
  
  <p>I was hoping that more experienced folks could advise me on something that was faster.</p>
  
  <p>What I need to do is to take a list of records on a sheet that contain up to 30 material columns of information (Target and Actual batch weight amounts.) I need to group each of these lines by material into a relative time bucket (12:00 AM to 12:59 AM, 1:00 AM to 1:59 AM, etc.) The user can of course select with Time Bucket they want to view</p>
  
  <p>I am using the sumifs function with two critieria ( i.e. Time >=12:00 and Time &lt; 1:00 ) to get the hourly buckets.</p>
  
  <pre><code>    You can also see from this code that I have to count the number of lines for the data and each criteria value because I could not figure out how to set the range of "B" &amp; "C" without counting. Since I am using a filter, I know that the ranges (from a row perspective) of A,B &amp; C are the same, just the relative columns are different. I tried to use the offset function, (i.e. Range(B) = Range(A).Offset(-1,0).Select or B = A.offset(-1,0).Select but they failed for some reason, and no error messages either. I think I somehow turned the erroring off.
  </code></pre>
  
  <p>Anyway, long story, really could use some help. Here is the related code:</p>
  
  <pre><code>Function Vis(Rin As Range) As Range
  'Returns the subset of Rin that is visible
  Dim Cell As Range
  'Application.Volatile
  Set Vis = Nothing
  For Each Cell In Rin
  If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
  If Vis Is Nothing Then
  Set Vis = Cell
  Else
  Set Vis = Union(Vis, Cell)
  End If
  End If
  Next Cell
  End Function
  
  Function SUMIFv(Rin As Range, CriteriaRange1 As Range, CriteriaValue1 As Variant, CriteriaRange2 As Range, CriteriaValue2 As Variant) As Long
  'Same as Excel SUMIFS worksheet function, except does not count
  'cells that are hidden
  Dim A1() As Range
  Dim B1() As Range
  Dim C1() As Range
  Dim Csum As Long
  ' First count up the number of ranges
  Cnt = 0
  For Each A In Vis(Rin).Areas
  Cnt = Cnt + 1
  Next A
  
  ReDim A1(1 To Cnt)
  ReDim B1(1 To Cnt)
  ReDim C1(1 To Cnt)
  
  CntA = 1
  For Each A In Vis(Rin).Areas
  Set A1(CntA) = A
  CntA = CntA + 1
  Next A
  CntB = 1
  For Each B In Vis(CriteriaRange1).Areas
  Set B1(CntB) = B
  CntB = CntB + 1
  Next B
  CntC = 1
  For Each C In Vis(CriteriaRange2).Areas
  Set C1(CntC) = C
  CntC = CntC + 1
  Next C
  
  If CntA &lt;&gt; CntB Or CntB &lt;&gt; CntC Then
  MsgBox ("Error in Sumifs Function: Counts from Ranges are not the same")
  End If
  Csum = 0
  For Cnt = 1 To CntA - 1
  Csum = Csum + WorksheetFunction.SumIfs(A1(Cnt), B1(Cnt), CriteriaValue1, C1(Cnt), CriteriaValue2)
  Next
  SUMIFv = Csum
  End Function
  </code></pre>
  
  <p>((countifv visible cells only</p>
  
  <p>If you are interested, here is a more general COUNTIF solution, and one that you can also apply to SUM and other functions that operate on ranges of cells.</p>
  
  <p>This COUNTIFv UDF uses the worksheet function COUNTIF to count visible cells only, so the Condition argument works the same as with COUNTIF. So you can use it just as you would COUNTIF:</p>
  
  <pre><code>=COUNTIFv(A1:A100,1)
  </code></pre>
  
  <p>Note that it uses a helper function (Vis) that returns the disjoint range of visible cells in a given range. This can be used with other worksheet functions to cause them to operate only on the visible cells. For example,</p>
  
  <pre><code>=SUM(Vis(A1:A100))
  </code></pre>
  
  <p>yields the sum of the visible cells in A1:A100. The reason why this approach of using Vis directly in the argument list does not work with COUNTIF is that COUNTIF will not accept a disjoint range as an input, whereas SUM will.</p>
  
  <p>Here's the UDF code:</p>
  
  <pre><code>Function Vis(Rin As Range) As Range
  'Returns the subset of Rin that is visible
  Dim Cell As Range
  Application.Volatile
  Set Vis = Nothing
  For Each Cell In Rin
  If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
  If Vis Is Nothing Then
  Set Vis = Cell
  Else
  Set Vis = Union(Vis, Cell)
  End If
  End If
  Next Cell
  End Function
  
  Function COUNTIFv(Rin As Range, Condition As Variant) As Long
  'Same as Excel COUNTIF worksheet function, except does not count
  'cells that are hidden
  Dim A As Range
  Dim Csum As Long
  Csum = 0
  For Each A In Vis(Rin).Areas
  Csum = Csum + WorksheetFunction.CountIf(A, Condition)
  Next A
  COUNTIFv = Csum
  End Function ))
  </code></pre>
  
  <p>this both are countif and sumifs with visible cells but i need sumif not sumifs please edit the 2nd code and compile and post the correct program :)</p>