+ Reply to Thread
Results 1 to 8 of 8

SUMIFS from different sheets

  1. #1
    Registered User
    Join Date
    02-06-2019
    Location
    Orlando, FL
    MS-Off Ver
    Excel for Mac 16.21.1
    Posts
    4

    SUMIFS from different sheets

    Hello!

    I have this formula working fine

    =SUMIFS(N:N,Q:Q,Z4,G:G,">="&DATE(2017,1,1),G:G,"<="&DATE(2017,1,31))

    on different sheets name January, February, March, etc etc to December

    I'm trying to bring that formula to a new sheet name Compare where I can display the results from these other sheets (January, February, etc) but I can't find a way to do it, I'm getting errors all the time

    I appreciate your feedback. Thanks!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS from different sheets

    Hello & Welcome to the Forum,

    You would need to use the indirect function to pull from the desired sheet.

    On your Compare sheet, enter January in A2 and then in B2 >>=INDIRECT("'"&A2&"'!K7")

    Change K7 to the cell that contains the formula on the January tab.

    Now list in A3 and down the other sheets and then drag down the B2 formula.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    02-06-2019
    Location
    Orlando, FL
    MS-Off Ver
    Excel for Mac 16.21.1
    Posts
    4

    Re: SUMIFS from different sheets

    Hi jefferbrown, thanks for your quick reply!

    I did work , thanks! but I have another issue..

    The formula on January tab is part of a table, and that table is getting sort multiple times , that makes my numbers to change on the cell constantly.. any ideas what can i do? thanks a lot!

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS from different sheets

    Not really sure. Can you provide a sample workbook that can explain the problem?

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    02-06-2019
    Location
    Orlando, FL
    MS-Off Ver
    Excel for Mac 16.21.1
    Posts
    4

    Re: SUMIFS from different sheets

    I attached the file..

    The formula that I'm trying to use is on January -> Cell AA4 AA5 AA6 etc... its a second table in the right side of the January sheet

    and the idea is to display the values on the sheet Compare

    The problem that I have now is that if I sort the table on January lets say by Source name or 2017 my values on Compare sheet are all wrong

    does it make sense?

    thanks again for your help!
    Attached Files Attached Files

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS from different sheets

    How about in C5 on the Compare sheet...

    =INDEX(January!AA:AA,MATCH($B5,January!$Z:$Z,0))

  7. #7
    Registered User
    Join Date
    02-06-2019
    Location
    Orlando, FL
    MS-Off Ver
    Excel for Mac 16.21.1
    Posts
    4

    Re: SUMIFS from different sheets

    I think it works!!

    Do you mind explain me a little bit the logic behind the formula? I'm trying to understand it, but I'm not sure if I get it... buy hey! it works

    Thanks a lot!!!!

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS from different sheets

    You are very welcome and thanks for the feedback.

    For the explanation, see if this helps.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. [SOLVED] Sumifs multiple sheets
    By aneliya in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-26-2017, 06:46 AM
  2. sumifs using ID over multiple sheets
    By Phillystones in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2016, 01:42 PM
  3. [SOLVED] sumifs over varing sheets
    By hänschendampf in forum Excel General
    Replies: 21
    Last Post: 04-08-2016, 07:50 AM
  4. [SOLVED] SUMIFS across multiple sheets
    By keith740 in forum Excel General
    Replies: 12
    Last Post: 06-08-2015, 04:51 PM
  5. SUMIFS across 2 sheets
    By soph239 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-12-2015, 11:43 AM
  6. [Question] Sumifs multiple sheets with flexible sheets reference
    By tranhaithang in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2014, 04:53 AM
  7. Sumifs across sheets
    By bestephens in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-25-2011, 01:58 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