+ Reply to Thread
Results 1 to 2 of 2

Summing with multiple criteria across mutilple identical tables across multiple worksheets

  1. #1
    Registered User
    Join Date
    06-13-2013
    Location
    New Mexico
    MS-Off Ver
    2016
    Posts
    16

    Summing with multiple criteria across mutilple identical tables across multiple worksheets

    I have 20 identical worksheets with identical tables in each. I need to (on a separate worksheet) sum the columns [TOTAL COST] based on the values in the columns [MATERIAL ACCT#] & [ACCT TO BE DEBITED:]. Of course, I could use the super long formula, but know there's an easier way. I am not versed in Macros or VBA, yet!

    My super long sample formula would begin like the one below, but of course, go out to 20 tables:

    =SUM((SUMIFS(COSTSHEET1[TOTAL COST],COSTSHEET1[MATERIAL ACCT'#],B26,COSTSHEET1[ACCT TO BE DEBITED:],2003.3)),(SUMIFS(COSTSHEET2[TOTAL COST],COSTSHEET2[MATERIAL ACCT'#],B26,COSTSHEET2[ACCT TO BE DEBITED:],2003.3)),(SUMIFS(COSTSHEET3[TOTAL COST],COSTSHEET3[MATERIAL ACCT'#],B26,COSTSHEET3[ACCT TO BE DEBITED:],2003.3)))

    Thank you!!!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Summing with multiple criteria across mutilple identical tables across multiple worksh

    Stating the obvious perhaps but you should have all your data in a single table on one sheet.

    I suggest since there are only 20 sheets you manually copy each table, and stack them up on a single sheet by pasting them underneath each other. That would be quicker than writing a macro and debugging it to perform the same task.

    In the process add an extra column to the big table to identify for each set of records a suitable code that indicates whatever it is each of your 20 sheets represents.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding Multiple Identical tables to a Master Identical table
    By dlogfx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2017, 04:41 PM
  2. [SOLVED] Summing data across multiple criteria on multiple worksheets
    By PDBartlett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2015, 11:57 AM
  3. Replies: 7
    Last Post: 05-24-2015, 02:47 PM
  4. Link multiple, identical graphs to multiple data tables
    By HJII in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-05-2014, 10:02 AM
  5. Summing with Multiple Criteria over 3 Worksheets
    By Kooksmagooks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2013, 02:25 PM
  6. Summing across multiple worksheets with criteria
    By HamnEgg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-02-2008, 06:22 AM
  7. summing multiple worksheets with multiple criteria
    By spirodem in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-31-2005, 11:17 PM

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