+ Reply to Thread
Results 1 to 3 of 3

Trying to sumif across sheets. SUMPRODUCT(SUMIF(INDIRECT function returns #REF error!

  1. #1
    Registered User
    Join Date
    06-21-2017
    Location
    New York NY
    MS-Off Ver
    2013
    Posts
    6

    Post Trying to sumif across sheets. SUMPRODUCT(SUMIF(INDIRECT function returns #REF error!

    Hello,

    I'm new here, hope you experts can find me a solution.

    I would like to sumif across multiple sheets. Each tab represents one week, each row represents one property. I had previously created a formula that would look up the property and sum across all weeks to get a running YTD total, which worked fine, but now that same formula when applied here does not work.
    Here is the formula I used:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&weeks&"'!"&"a1:a212"),$A7,INDIRECT("'"&weeks&"'!"&"C1:C212")))

    Where "weeks" is a named list of sheet names, the locations are all in Column A, and the sum range is in Column C

    Any idea why this is not working? I am getting #REF error

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    9,023

    Re: Trying to sumif across sheets. SUMPRODUCT(SUMIF(INDIRECT function returns #REF error!

    Attach a sample workbook (not a picture). 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.

  3. #3
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Trying to sumif across sheets. SUMPRODUCT(SUMIF(INDIRECT function returns #REF error!

    Your problem is coming from the INDIRECT clauses, but we have to see the definition of weeks to fix it. Try this: In the formula bar, select =SUMPRODUCT(SUMIF(INDIRECT("'"&weeks&"'!"&"a1:a212"),$A7,INDIRECT("'"&weeks&"'!"&"C1:C212"))) and press F9. Does the address look correct?

+ 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 Sumproduct, Countif/sumif, & Indirect on outside workbook
    By mamachrissy1028 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2015, 01:12 PM
  2. [SOLVED] SUMPRODUCT, SUMIF, INDIRECT and ADDRESS
    By dma1976 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2015, 10:11 AM
  3. [SOLVED] INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH
    By DJDRU in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2014, 08:42 AM
  4. Replies: 0
    Last Post: 08-17-2012, 02:02 PM
  5. [SOLVED] Sumif function returns a #Value! error
    By Kalts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2012, 07:50 AM
  6. Sumproduct(sumif(indirect
    By jmcgallan in forum Excel General
    Replies: 6
    Last Post: 09-17-2010, 12:03 PM
  7. 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

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