+ Reply to Thread
Results 1 to 6 of 6

INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

  1. #1
    Registered User
    Join Date
    02-18-2014
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    54

    INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

    Hello All,

    I received a lot of help yesterday on the forum but I have hit yet another snag. The first formula below which is in cell N10 is returning a 0 and I'm not sure why but it should be a 1. I am trying to reference cells C:Q ("C"&MATCH(E10,RL!A:A,0)&":Q"&MATCH(E10,RL!A:A,0)) on sheet RL in the indirect formula but I don't know what I am missing. Any help is appreciated! Thank you.

    =SUMIF(RL!C$28:Q$28,">="&M10,INDIRECT("C"&MATCH(E10,RL!A:A,0)&":Q"&MATCH(E10,RL!A:A,0)))

    This formula is in cell N11 which works if the information is all on the same sheet

    =SUMIF(S$9:AG$9,">="&M11,INDIRECT("S"&MATCH(E11,Q:Q,0)&":AG"&MATCH(E11,Q:Q,0)))
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP 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
    29,464

    Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

    Hi,

    Why do you believe that Item Ref 444 on 9/25/14 should return a total of 1?
    Cell H29 (which is the intersection of 9/25/14 and 444) is zero.

    One other Q. In your real world production workbook are there many rows for the same Item Ref? If not you don't need SUMIF.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Cool Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

    I have made a small adjustment in your formula...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check the attached file...
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  4. #4
    Registered User
    Join Date
    02-18-2014
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    54

    Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

    Quote Originally Posted by Vikas_Gautam View Post
    I have made a small adjustment in your formula...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check the attached file...
    Awesome! It works just as I wanted it to. I was so close! Thank you very much for your help.

  5. #5
    Registered User
    Join Date
    02-18-2014
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    54

    Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Why do you believe that Item Ref 444 on 9/25/14 should return a total of 1?
    Cell H29 (which is the intersection of 9/25/14 and 444) is zero.

    One other Q. In your real world production workbook are there many rows for the same Item Ref? If not you don't need SUMIF.
    The item number would typically only show once on MD! but depending on the event column it may show multiple times. I thought the SUMIF function was referring to comparing the date on MD! column M to the date on the corresponding row of the item ref, MD! column E, and then to SUMIF values greater or equal to the date MD! Column M. Is there a more efficient way? I want to make this as light as possible.

  6. #6
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH

    Thanks for the response DJ..

+ 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. Using Indirect function to sum across multiple sheets
    By Irishman07 in forum Excel General
    Replies: 10
    Last Post: 02-14-2018, 05:26 PM
  2. [SOLVED] Help with INDIRECT function w/ multiple sheets
    By b0ttle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-20-2014, 11:57 PM
  3. Replies: 0
    Last Post: 08-17-2012, 02:02 PM
  4. [SOLVED] indirect function within sumif to reference other sheets
    By stacrafty@yahoo.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2006, 12:50 PM
  5. Sum Indirect function through multiple sheets
    By Andre Croteau in forum Excel General
    Replies: 2
    Last Post: 05-06-2005, 06:06 AM

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