Solved

# SUMPRODUCT Multiple Sheets

Posted on 2014-07-10
331 Views
Last Modified: 2014-07-14
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
0
Question by:jmac001
3 Comments

Expert Comment

I have the same challenge. Following.
0

LVL 20

Accepted Solution

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

Author Closing Comment

Thanks very much
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

#### 745 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!