Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF | Excel Worksheet Functions | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |