+ Reply to Thread
Results 1 to 7 of 7

SUMIF INDIRECT formula does not wok on similat worksheets

  1. 05-25-2022, 01:43 AM #1
    wolfm
    wolfm is offline
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    95

    SUMIF INDIRECT formula does not wok on similat worksheets

    I have a function that works great, in one worksheet:
    Please Login or Register  to view this content.
    When I copy and paste it into another worksheet, I get a #REF! error. Have spent over 3 hours trying to figure out why it works on one worksheet, but not another, in the same workbook.
    They do reference different worksheets, but I'm thinking the formula should work the same.

    For example, I have this table:
    Screenshot 2022-05-25 013535.jpg
    If I place the above formula into cell E5 of this table, I get the #REF! error, not a total of JT's sales.


    I am trying to reference the sheet 'Jan KDS' with the value in cell B5 of the chart. Then, to pull the value in cell E4 of the chart "JT" from column C in 'Jan KDS', and then sum the total in Column R which match the rows containing "JT" in column C.
    Screenshot 2022-05-25 013812.jpg


    I just can't wrap my head around this. Any pointers would be greatly appreciated. Thanks.
    Reply With QuoteRegister To Reply
  2. 05-25-2022, 01:57 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,181

    Re: SUMIF INDIRECT formula does not wok on similat worksheets

    Try this:

    =SUMIF(INDIRECT("'"&$B5&"'!C:C"),E$4 & "*",INDIRECT("'"&$B5&"'!R:R"))

    Failing that, as screenshots are of little practical value, prepare and attach a small sample workbook to demonstrate the problem.

    Administrative Note:

    Members will tailor the solutions they offer to the version (NOT a release number like Excel version 14.0.6123.5001) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 05-25-2022, 05:52 AM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: SUMIF INDIRECT formula does not wok on similat worksheets

    As you have a space in the sheet name 'Jan KDS', you will need to include those apostrophes, so try it this way:

    =SUMIF(INDIRECT("'"& $B5 & "'!C:C"),E$4 & "*",INDIRECT("'"& $B5 & "'!R:R"))

    Ensure that you don't have any leading or trailing spaces in the tab name and in column B.

    Hope this helps.

    Pete

    EDIT: Sorry Ali, I thought yours was just an Admin note on first reading - I now see that you have provided the same solution.

    Pete
    Last edited by Pete_UK; 05-25-2022 at 05:55 AM.
    Reply With QuoteRegister To Reply
  4. 05-25-2022, 06:24 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,181

    Re: SUMIF INDIRECT formula does not wok on similat worksheets

    Yes, I did, but I don't think the OP has seen it, either!!! At least your saying the same thing will reinforce my suggestion.
    Reply With QuoteRegister To Reply
  5. 05-25-2022, 06:42 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: SUMIF INDIRECT formula does not wok on similat worksheets

    FWIW... running SUMIF with INDIRECT is at least 10 x slower than using SUMIF without INDIRECT. So, don't use whole column ranges... use sensible, future-proof ranges, or dynamic named ranges to speed up the initital calculation, and to minimise recalculation times (INDIRECT recalculates every time anything changes).
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  6. 05-25-2022, 09:35 AM #6
    wolfm
    wolfm is offline
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    95

    Re: SUMIF INDIRECT formula does not wok on similat worksheets

    Ali and Pete: This works! Also, it explains why the formula worked on other worksheets that only have a one-word name. And so, covering for the space in the tab name certainly makes sense. Thanks so much to the both of you! I can always count on this forum to give me workable solutions!

    Glenn: Great point and noted for the continued improvement of this complicated workbook I have acquired!
    Reply With QuoteRegister To Reply
  7. 05-25-2022, 10:20 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,181

    Re: SUMIF INDIRECT formula does not wok on similat worksheets

    You are most welcome and thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Alternative to INDIRECT to SUMIF in multiple worksheets
    By ongcaps in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-21-2018, 06:47 AM
  2. [SOLVED] sumif multiple criteria with indirect formula
    By Neilesh Kumar in forum Excel General
    Replies: 0
    Last Post: 02-19-2018, 11:01 AM
  3. [SOLVED] Sumproduct-Sumif-Indirect Formula Problem
    By PJH008 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-08-2016, 04:52 AM
  4. [SOLVED] Sumif with INDIRECT formula
    By wiewie002 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-22-2015, 09:20 PM
  5. [SOLVED] VBA Formula SUMIF with INDIRECT performance issue
    By ffasan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2014, 08:33 AM
  6. [SOLVED] Need help! Using the INDIRECT function within a SUMIF formula
    By Kshari in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-02-2014, 07:27 PM
  7. Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria
    By robgardner15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 02:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1