Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Further to a post yesterday looking at Countif3d, I am now trying to use a
similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Joanne,
The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Excellent, thank you so much. I had to use ";" instead of "," in the formula
but it works exactly! What a star! "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Sorry I now have another issue with this formula:
When I insert rows on the worksheets that the function is searching, the range in the formula does not expand like they normally do. I even tried using the "$" sign but it didn't work. Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31) or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank 2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x") If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10, it stays the same. It does not count the values in row 11. Thanks in advance "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
The function takes a string as the address, so you would need to create the string. Perhaps,
=CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Sorry I now have another issue with this formula: When I insert rows on the worksheets that the function is searching, the range in the formula does not expand like they normally do. I even tried using the "$" sign but it didn't work. Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31) or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank 2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x") If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10, it stays the same. It does not count the values in row 11. Thanks in advance "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
No joy I'm afraid, this formula continues to only count within rows 9 and 10.
I know thats it's possible to do this, because with normal 'Countif' 2d it extends the range when a row is inserted. Any other suggestions? Thanks Joanne "Bernie Deitrick" wrote: The function takes a string as the address, so you would need to create the string. Perhaps, =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Sorry I now have another issue with this formula: When I insert rows on the worksheets that the function is searching, the range in the formula does not expand like they normally do. I even tried using the "$" sign but it didn't work. Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31) or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank 2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x") If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10, it stays the same. It does not count the values in row 11. Thanks in advance "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Joanne,
You need to set up the formula before you insert the row between A9 and A10 on sheet "Blank 1" The formula should update from =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) to =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A11),COLUMN('Blank 1'!A11));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... No joy I'm afraid, this formula continues to only count within rows 9 and 10. I know thats it's possible to do this, because with normal 'Countif' 2d it extends the range when a row is inserted. Any other suggestions? Thanks Joanne "Bernie Deitrick" wrote: The function takes a string as the address, so you would need to create the string. Perhaps, =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Sorry I now have another issue with this formula: When I insert rows on the worksheets that the function is searching, the range in the formula does not expand like they normally do. I even tried using the "$" sign but it didn't work. Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31) or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank 2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x") If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10, it stays the same. It does not count the values in row 11. Thanks in advance "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Hi Bernie
It works when I insert a row in Blank 1, but I'm sorry my workbook is a lot more complicated than that. Maybe I should have explained earlier. Blank 1 and Blank 2 are like 'bookends', they will eventually be hidden from the user. As I am designing a template, there will be a variable number of worksheets in between these 2 bookends. Therefore there will also be a different number of rows on each worksheet. Each worksheet starts out the same when inserted, as this is done using a macro to standardise formatting. At the moment the formulas work when more worksheets are entered, the problem will come when the user will insert more rows. Sorry to complicate the problem, Thanks "Bernie Deitrick" wrote: Joanne, You need to set up the formula before you insert the row between A9 and A10 on sheet "Blank 1" The formula should update from =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) to =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A11),COLUMN('Blank 1'!A11));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... No joy I'm afraid, this formula continues to only count within rows 9 and 10. I know thats it's possible to do this, because with normal 'Countif' 2d it extends the range when a row is inserted. Any other suggestions? Thanks Joanne "Bernie Deitrick" wrote: The function takes a string as the address, so you would need to create the string. Perhaps, =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Sorry I now have another issue with this formula: When I insert rows on the worksheets that the function is searching, the range in the formula does not expand like they normally do. I even tried using the "$" sign but it didn't work. Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31) or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank 2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x") If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10, it stays the same. It does not count the values in row 11. Thanks in advance "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Joanne,
You could set the formula up to check many more rows than expected - it shouldn't matter, if there is no data in those ranges. The other option is to sum the results of COUNTIF formulas (located in the same, unvarying cell) on each sheet. The real bottom line is that Excel doesn't do a good job with multiple sheets. Often, the best design is to use one sheet with another column of identifiers - value that you have been using as the sheet name, perhaps - and then using various tools such as data filters with SUBTOTAL functions, and/or pivot tables to handle the data analysis. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hi Bernie It works when I insert a row in Blank 1, but I'm sorry my workbook is a lot more complicated than that. Maybe I should have explained earlier. Blank 1 and Blank 2 are like 'bookends', they will eventually be hidden from the user. As I am designing a template, there will be a variable number of worksheets in between these 2 bookends. Therefore there will also be a different number of rows on each worksheet. Each worksheet starts out the same when inserted, as this is done using a macro to standardise formatting. At the moment the formulas work when more worksheets are entered, the problem will come when the user will insert more rows. Sorry to complicate the problem, Thanks "Bernie Deitrick" wrote: Joanne, You need to set up the formula before you insert the row between A9 and A10 on sheet "Blank 1" The formula should update from =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) to =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A11),COLUMN('Blank 1'!A11));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... No joy I'm afraid, this formula continues to only count within rows 9 and 10. I know thats it's possible to do this, because with normal 'Countif' 2d it extends the range when a row is inserted. Any other suggestions? Thanks Joanne "Bernie Deitrick" wrote: The function takes a string as the address, so you would need to create the string. Perhaps, =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Sorry I now have another issue with this formula: When I insert rows on the worksheets that the function is searching, the range in the formula does not expand like they normally do. I even tried using the "$" sign but it didn't work. Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31) or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank 2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x") If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10, it stays the same. It does not count the values in row 11. Thanks in advance "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct 3d
Thank you Bernie
I think setting it to check many more rows than expected is the best way forward. That seems to work. Thanks again for all your help. Joanne "Bernie Deitrick" wrote: Joanne, You could set the formula up to check many more rows than expected - it shouldn't matter, if there is no data in those ranges. The other option is to sum the results of COUNTIF formulas (located in the same, unvarying cell) on each sheet. The real bottom line is that Excel doesn't do a good job with multiple sheets. Often, the best design is to use one sheet with another column of identifiers - value that you have been using as the sheet name, perhaps - and then using various tools such as data filters with SUBTOTAL functions, and/or pivot tables to handle the data analysis. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hi Bernie It works when I insert a row in Blank 1, but I'm sorry my workbook is a lot more complicated than that. Maybe I should have explained earlier. Blank 1 and Blank 2 are like 'bookends', they will eventually be hidden from the user. As I am designing a template, there will be a variable number of worksheets in between these 2 bookends. Therefore there will also be a different number of rows on each worksheet. Each worksheet starts out the same when inserted, as this is done using a macro to standardise formatting. At the moment the formulas work when more worksheets are entered, the problem will come when the user will insert more rows. Sorry to complicate the problem, Thanks "Bernie Deitrick" wrote: Joanne, You need to set up the formula before you insert the row between A9 and A10 on sheet "Blank 1" The formula should update from =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) to =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A11),COLUMN('Blank 1'!A11));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... No joy I'm afraid, this formula continues to only count within rows 9 and 10. I know thats it's possible to do this, because with normal 'Countif' 2d it extends the range when a row is inserted. Any other suggestions? Thanks Joanne "Bernie Deitrick" wrote: The function takes a string as the address, so you would need to create the string. Perhaps, =CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" & ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31) HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Sorry I now have another issue with this formula: When I insert rows on the worksheets that the function is searching, the range in the formula does not expand like they normally do. I even tried using the "$" sign but it didn't work. Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31) or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank 2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x") If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10, it stays the same. It does not count the values in row 11. Thanks in advance "Bernie Deitrick" wrote: Joanne, The function, as written, simply multiplies two ranges together and then sums. It does not handle the comparison automatically. It can be re-written to do so - try this version and use it like =SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x") Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _ sRng2 As String, Val2 As Variant) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2) Next i SumProduct3D2C = Sum End Function HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Further to a post yesterday looking at Countif3d, I am now trying to use a similar UDF for SumProduct 3d, but I keep getting the #Value! I found the macro on: http://www.dailydoseofexcel.com/arch...ned-functions/ (it has been modified a bit from the website based on a suggestion from the forum yesterday - which worked perfectly for countif3d) What am I doing wrong? Thank you in advance The formula I enter is: =SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank 2'!J9:J10"="x")) The macro for the function is: Function SumProduct3D2(sRng1 As String, sRng2 As String) _ As Variant Dim vaRng1 As Variant, vaRng2 As Variant Dim rTemp As Range Dim i As Long Dim Sum As Double Dim rCell As Range Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1) vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2) For i = LBound(vaRng1) To UBound(vaRng1) Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value) Next i SumProduct3D2 = Sum End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct contains | Excel Worksheet Functions | |||
SumProduct | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct help | Excel Worksheet Functions |