SUMPRODUCT Multiple Sheets

Posted on 2014-07-10
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?

Question by:jmac001

    Expert Comment

    by:Joe Carman
    I have the same challenge. Following.
    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

    Open in new window

    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.

    Author Closing Comment

    Thanks very much

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    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.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now