+ 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
    13

    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 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
    26,827

    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

    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