+ Reply to Thread
Results 1 to 6 of 6

IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

  1. #1
    Registered User
    Join Date
    11-20-2019
    Location
    Ohio
    MS-Off Ver
    Microsoft Office Home and Business 2019
    Posts
    2

    IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

    I want to know if there's a formula for the following--
    Basically -- on sheet 2 for the name of an insurance, I want it to take the sum total for the same insurance on sheet 1.
    I have 4 agencies I will need to create spreadsheets for. Not all insurances are in every agency. So I'm hoping there's an easy way to have a formula match the contents of the cell instead of having to put in a formula with every single insurance name (there's over 200).
    Hope I've explained it well enough. :D

    Thanks for any help!
    Jen
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

    yes, try
    =SUMIF(Sheet1!H:H,Sheet2!A11,Sheet1!K:K)
    BUt not sure which column you want to add up, i have chosen K
    but change that column to what you want to sum
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    11-20-2019
    Location
    Ohio
    MS-Off Ver
    Microsoft Office Home and Business 2019
    Posts
    2

    Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

    Thank you! It's even super simple! I was making it way too hard.

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

    Hi -

    I think this is pretty straight forward, unless I'm missing something. I'm using your highlighted example for the general formula. So on Sheet 2 enter this formula in cell B11:

    =SUMIF(Sheet1!$H$2:$H$27,Sheet2!A11,Sheet1!$J$2:$J$28)

    This will sum up the Balance Column for MDHHS.

    Just copy the formula up and down to get the sums for each of the other names.

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

    you are welcome

  6. #6
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

    =SUMPRODUCT(SUMIF(INDIRECT(CONCATENATE(_sh,"!H2:H27")),$A11,INDIRECT(CONCATENATE(_sh,"!J2:J27"))))
    _sh is a name for sheets to consolidate
    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)

Similar Threads

  1. Replies: 1
    Last Post: 07-12-2018, 11:46 AM
  2. [SOLVED] Sumif formula multiple sheets
    By AzzKikA in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-07-2015, 12:22 AM
  3. Macro to filter multiple columns of data to multiple work sheets
    By LISSANN in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2014, 07:24 PM
  4. Sumif or Index formula across multiple sheets
    By achenbachwb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-18-2013, 04:14 PM
  5. Replies: 0
    Last Post: 08-17-2012, 02:02 PM
  6. SUMIF Formula Across Multiple Sheets
    By El Guapo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2012, 04:13 AM
  7. [SOLVED] SUMIF which work's with multiple sheets
    By Mikus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-29-2005, 05:05 PM

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