Sumproduct Multiple Worksheets, Columns and Rows

Peter

As you already have some code in your workbook, how about building a UDF to resolve this.

Insert the function below into one of your general modules.

Function MySUMPRODUCT(Divn As Range, Dte, Itm As String)
'AUTHOR: rylo
'DATE: 6/8/07
'REFERENCE: http://www.excelforum.com/showthread.php?t=609767
  Dim WrkSH As Worksheet
  holder = 0
  For Each dn In Divn
    If dn Like "Division*" Then
      Set WrkSH = Sheets(dn.Value)
      On Error Resume Next
      holder = holder + WrkSH.Cells(WorksheetFunction.Match(Itm, WrkSH.Range("A:A"), 0), WorksheetFunction.Match(Dte, WrkSH.Range("5:5"), 0))
      On Error GoTo 0
    End If
  Next dn
  MySUMPRODUCT = holder
End Function

This would then be called as per normal in sheet summary
F6: =mysumproduct(modWorksheetsSelected,$F$3,C6)


HTH

rylo

Thanks Rylo ... wow ... u are obviously a wizard

Rylo ... your function works well .. however if there is more than one instance of the Account Names in column A, I need the function to include that value as well.

By way of example, on the DivisionA worksheet, if there is another amount for rental income on row 10 (in addition to row7) then i need the formula to include this amount as well.

Thanks in advance

Peter

how about

Function MySUMPRODUCT2(Divn As Range, Dte, Itm As String)
'AUTHOR: rylo
'DATE: 6/8/07
'REFERENCE: http://www.excelforum.com/showthread.php?t=609767
  
  Dim WrkSH As Worksheet, holder As Long, findit As Range, firstadd As String
  Application.Volatile
  holder = 0
  For Each dn In Divn
    If dn Like "Division*" Then
      Set WrkSH = Sheets(dn.Value)
      With WrkSH
        Set findit = .Range("A:A").Find(what:=Itm)
        If Not findit Is Nothing Then
          firstadd = findit.Address
          coloff = WorksheetFunction.Match(Dte, WrkSH.Range("5:5"), 0) - 1
          Do
            holder = holder + findit.Offset(0, coloff)
            Set findit = .Range("A:A").Find(what:=Itm, after:=findit)
          Loop Until findit.Address = firstadd
        End If
      End With
    End If
  Next dn
  MySUMPRODUCT2 = holder
End Function

rylo

Here is a Formula version. Sample workbook below.