+ Reply to Thread
Results 1 to 13 of 13

Lookup and sum values across multiple worksheets

  1. #1
    Registered User
    Join Date
    02-24-2013
    Location
    USA
    MS-Off Ver
    Microsoft Excel for Mac v16.38
    Posts
    66

    Lookup and sum values across multiple worksheets

    I am trying to build a summary report from 12 individual worksheets.

    Row 1 = revenue item
    Column B-L = month

    I would like to sum the values for the matched row across all 12 sheets.

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    938

    Re: Lookup and sum values across multiple worksheets

    Please Login or Register  to view this content.
    Drag across.

    Pete

  3. #3
    Registered User
    Join Date
    02-24-2013
    Location
    USA
    MS-Off Ver
    Microsoft Excel for Mac v16.38
    Posts
    66

    Re: Lookup and sum values across multiple worksheets

    That works if I dont have to search for the value in column A.
    I have to sum the values in each month for the respective row label for the month

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    938

    Re: Lookup and sum values across multiple worksheets

    Might try:

    Please Login or Register  to view this content.
    (Could bog things down if there's lots of data to evaluate)

    If that doesn't do it, a sanitized sample file for the forum members to look at would help.

    Pete

  5. #5
    Registered User
    Join Date
    02-24-2013
    Location
    USA
    MS-Off Ver
    Microsoft Excel for Mac v16.38
    Posts
    66

    Re: Lookup and sum values across multiple worksheets

    I am sorry if I am being unclear. Let me try again.

    I need to match each sheet row value to the the summary sheet row value adn sum, the corresponding matched month.

    For example, I want to sum the values for all rows matching the value "410100 Rent - Seasonal RV" in row 8 in column D "Feb 21" for all the sheets from Bamboo to Sundowner.

    Does this help explain int better?

  6. #6
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    938

    Re: Lookup and sum values across multiple worksheets

    Thanks ara.darakjian.

    A sample file would eliminate all ambiguity.

    Thanks.

  7. #7
    Registered User
    Join Date
    02-24-2013
    Location
    USA
    MS-Off Ver
    Microsoft Excel for Mac v16.38
    Posts
    66

    Re: Lookup and sum values across multiple worksheets

    I attached one. I will attached again.

  8. #8
    Registered User
    Join Date
    02-24-2013
    Location
    USA
    MS-Off Ver
    Microsoft Excel for Mac v16.38
    Posts
    66

    Re: Lookup and sum values across multiple worksheets

    Did this file help?

  9. #9
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    938

    Re: Lookup and sum values across multiple worksheets

    There's no file.

  10. #10
    Registered User
    Join Date
    02-24-2013
    Location
    USA
    MS-Off Ver
    Microsoft Excel for Mac v16.38
    Posts
    66

    Re: Lookup and sum values across multiple worksheets

    Let me try again.
    my apologies
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    938

    Re: Lookup and sum values across multiple worksheets

    Not the best way, IMO but. . .

    Try this in SUM C2. Copy down and over.

    Please Login or Register  to view this content.
    Total in Col. N.

    Add sheet references as necessary.

    Pete

  12. #12
    Registered User
    Join Date
    02-24-2013
    Location
    USA
    MS-Off Ver
    Microsoft Excel for Mac v16.38
    Posts
    66

    Re: Lookup and sum values across multiple worksheets

    Thank you for the reply. Is there a way to simplify the formula so that I can use it with as many as 20 worksheet?

  13. #13
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    938

    Re: Lookup and sum values across multiple worksheets

    You'll need a list of sheet names.

    In the interest of not reinventing electricity. . .(Please remove if not allowed. . .)

    This thread will probably provide further help: https://www.automateexcel.com/formul...ltiple-sheets/

    Pete

+ 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. Lookup values from two different worksheets
    By Abdul Moix in forum Excel General
    Replies: 5
    Last Post: 08-07-2020, 02:27 PM
  2. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  3. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  4. Lookup Values, 2 Worksheets, one similar column
    By ladron in forum Excel General
    Replies: 5
    Last Post: 09-20-2012, 04:19 AM
  5. Sum values from multiple cells - multiple lookup values in single cell
    By taxdept in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 04:12 PM
  6. [SOLVED] Lookup values across range of worksheets
    By lucasreece in forum Excel General
    Replies: 3
    Last Post: 03-30-2012, 05:13 PM
  7. Lookup values from several different worksheets
    By gunerstahl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2011, 06:37 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