Sumif in 3d
 

I found a VBA solution to SUMIF in 3d however, it returned a Valu
error.
I need to sumif over 200 sheets. How can I do this

--
Beeblebro
-----------------------------------------------------------------------
Beeblebrox's Profile: http://www.excelforum.com/member.php...fo&userid=3734
View this thread: http://www.excelforum.com/showthread.php?threadid=57163

Sumif in 3d
 

that might be an indication that it wasn't actually a solution.

Without knowing what your solution was, it would be hard to say.

--
Regards,
Tom Ogilvy

"Beeblebrox" wrote
in message ...

I found a VBA solution to SUMIF in 3d however, it returned a Value
error.
I need to sumif over 200 sheets. How can I do this?


--
Beeblebrox
------------------------------------------------------------------------
Beeblebrox's Profile:
http://www.excelforum.com/member.php...o&userid=37348
View this thread: http://www.excelforum.com/showthread...hreadid=571630

Sumif in 3d
 

I'd check the data in each of those sheets, too.

Maybe you have a cell that evaluates to #value! in one of those sheets.

But it could be the code, too. If there are unhandled errors, you could see
that result, too.

Beeblebrox wrote:

I found a VBA solution to SUMIF in 3d however, it returned a Value
error.
I need to sumif over 200 sheets. How can I do this?

--
Beeblebrox
------------------------------------------------------------------------
Beeblebrox's Profile: http://www.excelforum.com/member.php...o&userid=37348
View this thread: http://www.excelforum.com/showthread...hreadid=571630

--

Dave Peterson

Sumif in 3d
 

Here's the coding I used. I'm testing conditions that are the text
results of a lookup and summing cells that are numerical results of
nested IF statements and a lookup). But I also tried it with replacing
the text lookup with straight text and the If formulas with numbers on
only three sheets. Still no luck.



Function SumIf3D(Range3D As String, Criteria As String, Optional
Sum_Range As Variant) As Variant

Dim sTestRange As String
Dim sSumRange As String
Dim Sheet1 As Integer
Dim Sheet2 As Integer
Dim n As Integer
Dim Sum As Double

Application.Volatile

If Parse3DRange(Application.Caller.Parent.Parent.Name , Range3D,
Sheet1, Sheet2, Sheet3, sTestRange) = False Then
SumIf3D = CVErr(xlErrRef)
End If

If IsMissing(Sum_Range) Then
sSumRange = sTestRange
Else
sSumRange = Sum_Range.Address
End If

Sum = 0
For n = Sheet1 To Sheet3
With Worksheets(n)
Sum = Sum +
Application.WorksheetFunction.SumIf(.Range(sTestRa nge), Criteria,
..Range(sSumRange))
End With
Next n
SumIf3D = Sum
End Function


--
Beeblebrox
------------------------------------------------------------------------
Beeblebrox's Profile: http://www.excelforum.com/member.php...o&userid=37348
View this thread: http://www.excelforum.com/showthread...hreadid=571630

Sumif in 3d
 

Did you check your data in each sheet for errors?

Do you have any hidden rows in any of the sheets that could have errors?

If you changed the sheets to a smaller group, did it work ok?

What was the formula you used in the cell?

Beeblebrox wrote:

Here's the coding I used. I'm testing conditions that are the text
results of a lookup and summing cells that are numerical results of
nested IF statements and a lookup). But I also tried it with replacing
the text lookup with straight text and the If formulas with numbers on
only three sheets. Still no luck.

Function SumIf3D(Range3D As String, Criteria As String, Optional
Sum_Range As Variant) As Variant

Dim sTestRange As String
Dim sSumRange As String
Dim Sheet1 As Integer
Dim Sheet2 As Integer
Dim n As Integer
Dim Sum As Double

Application.Volatile

If Parse3DRange(Application.Caller.Parent.Parent.Name , Range3D,
Sheet1, Sheet2, Sheet3, sTestRange) = False Then
SumIf3D = CVErr(xlErrRef)
End If

If IsMissing(Sum_Range) Then
sSumRange = sTestRange
Else
sSumRange = Sum_Range.Address
End If

Sum = 0
For n = Sheet1 To Sheet3
With Worksheets(n)
Sum = Sum +
Application.WorksheetFunction.SumIf(.Range(sTestRa nge), Criteria,
Range(sSumRange))
End With
Next n
SumIf3D = Sum
End Function

--
Beeblebrox
------------------------------------------------------------------------
Beeblebrox's Profile: http://www.excelforum.com/member.php...o&userid=37348
View this thread: http://www.excelforum.com/showthread...hreadid=571630

--

Dave Peterson