+ Reply to Thread
Results 1 to 5 of 5

Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF

  1. #1
    Registered User
    Join Date
    10-04-2005
    Posts
    5

    Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF

    I receive a #REF error for the formula I'm trying to work on. I would be really grateful for some insight.

    What I’m trying to do:

    I am working on a personal budget for home expenses. I would like to sum all the categories for each month so I can see what is the highest expense for each month. So in the formula, I am trying to find x (in this case, 'Groceries') for a given month and then calculate the total.

    Computer: MAC
    Workbook: Expenses.xlsx
    Sheets: Budget / OCT12 / NOV12 / DEC12 etc.

    Budget Sheet:

    A B C D
    28 DAILY LIVING OCT NOV DEC
    29 Groceries #REF
    30 Dining / Eating Out
    31 Pharmacy Supplies
    32 Dry Cleaning
    33 Salon / Hair
    34 Eyewear / Contacts
    35 Clothing
    36 Education / Lessons


    OCT12:

    A B C D
    58 DATE CATEGORY DESCRIPTION AMOUNT
    59 1-Oct-12 Bus / Taxi / MRT Taxi $10.00
    60 1-Oct-12 Dry Cleaning Astoria $10.00
    61 1-Oct-12 Groceries Jasons $10.00
    62 12-Oct-12 Groceries Market Place $50.00
    63 12-Oct-12 Pharmacy Supplies Guardian - Toiletries $30.00
    64 13-Oct-12 Groceries Marks & Spencer $60.00
    65 13-Oct-12 Hardware / DIY Services Home-Fix DIY $10.00
    66 16-Oct-12 Dining / Eating Out Starbucks Coffee $20.00
    etc.


    PLEASE NOTE: The B column (CATEGORY) allows you to select via a dropdown menu. Will this cause the formula not to work? I have indented the list on the dropdown to look like this ' Groceries', where as in the Budget Sheet A29, it shows 'Groceries'. This was so I could group categories and it was be easy on the eye when searching.

    The formula I used which caused an error in cell B29 is:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&OCT12&"'!B59:B110"),$A29,INDIRECT("'"&OCT12&"'!D59:D110")))

    Please could you let me know what the issue is.

    Thanks

    M

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF

    Hi

    i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF

    I'm afraid your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    http://www.mrexcel.com/forum/excel-q...shows-ref.html

  4. #4
    Valued Forum Contributor Dunc3142's Avatar
    Join Date
    09-05-2007
    Location
    Franklin OH
    MS-Off Ver
    2000 and 2007 and 2010 @ Home & Teach 2010
    Posts
    351

    Re: Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF

    See if this works for you..
    See attached:
    Attached Files Attached Files
    Yes it will. we just have to figure out how...
    If I have helped you, PLEASE click the * and add to my Rep.
    Also, if the problem is SOLVED please mark it as so.

  5. #5
    Registered User
    Join Date
    10-04-2005
    Posts
    5

    Re: Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF

    Apologies for cross-posting. I posted the link to the other forum.

    This has now been resolved. Thank you all for your help!


    http://www.mrexcel.com/forum/excel-q...ml#post3421649

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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