+ Reply to Thread
Results 1 to 1 of 1

If criteria match, multiply then sum across multiply worksheets

  1. #1
    Registered User
    Join Date
    03-06-2013
    Location
    SLC, UT
    MS-Off Ver
    Excel 2010
    Posts
    12

    If criteria match, multiply then sum across multiply worksheets

    I have a workbook that has many worksheets pertaining to a project - One for an original contract, one for each change order (for this example), a reference worksheet, and a Contract Summary page.

    Each worksheet has up to 75 rows of data. Column A is the FX (element of the project), B is the description, C is the quantity for each element, D is the Material Cost, E and F are two tax amounts, G-M are departments (for which hours are assigned), and N is a sum of C*D, C*E, C*F, etc.

    I have each worksheet set up to work properly EXCEPT the Contract Summary.

    I have created a SheetList to include all of the worksheets (except the summary and reference/Tax sheet).

    I have each FX that exists in either the Original Contract or any Change Order listed once.
    The contract Summary should add all of the totals from the SheetList for each FX.

    I have tried to accomplish this using a formula but it doesn't work.

    =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A76"),A2,INDIRECT("'"&SheetList&"'!D2:D76"))) will add all of the values where A2 in the Contract Summary matches A2:76 in the SheetList, but it will not multiply C2:C76 by that number first.

    I have also tried =IF("'"&SheetList&"'!A2:A76"=A2), SUMPRODUCT(INDIRECT("'"&SheetList&"'!C2:C76","'"&SheetList&"'!D2:D76")), but that returns a #VALUE error.

    I need a macro that will look at A2 of the Contract Summary and compare it to A2:A76 of each page in the SheetList. If there is a match, then I need it to multiply C2:C76 of the SheetList by D2:D76 and enter the sum of those products into D2 of the Contract Summary (to be repeated for columns E-M).

    The end result on the Contract Summary page for FX 1 should be this:

    Expected result.jpg
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1