# SUMPRODUCT Multiple Sheets

Hi I have problems working with SUMPRODUCTS on a good day.  I have a sample workbook that I would like to Sum columns f (qty needed); g (qty on hand) and h (missing) based on the text in column b (part number) for all tabs to the right of the Summary tab and return the results on the Summary worksheet. When the worksheet is complete there will 23 different tabs not including the Summary tab.  Is it possible to use SUMPRODUCT to do this?

Thanks.
EE-BOM-2014.07.10.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I have the same challenge. Following.
0
Commented:
Not sure it is possible to use multiple sheets in the Sumproduct function, but a VBA function can loop the sheets and do a SumIF on each.
``````Function SumIfSummary(Match As String, ColumnToMatch As String, ColumnToSum As String) As Double
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
SumIfSummary = SumIfSummary + WorksheetFunction.SumIf(ws.Range(ColumnToMatch & ":" & ColumnToMatch), Match, ws.Range(ColumnToSum & ":" & ColumnToSum))
End If
Next ws
End Function
``````

There are 3 arguments to the function
Match: The Part number in column C on sheet Summary.
ColumnToMatch: The column to match on the other sheets as text, "B".
ColumnToSum: The column to sum on the other sheets as text, "F" or "G" or "H".

See file.
EE-BOM-2014.07.10.xlsm
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks very much
0