+ Reply to Thread
Results 1 to 16 of 16

SUMPRODUCT using INDIRECT function

  1. #1
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    SUMPRODUCT using INDIRECT function

    Hi everyone,

    First time posting so excuse the messiness (not sure if the screen shots will show up either)

    I am trying to summarise data from multiple tabs however the tables I am referencing vary in size (vertically) across each of the tabs.

    I was hoping I could use SUMPRODUCT combined with INDIRECT to achieve this on my summary tab

    First I have made sure my SUMPRODUCT works on its own

    =SUMPRODUCT('Property 1'!AM7:AP172,('Property 1'!AM7:AP7=C1)*('Property 1'!AM7:AM172=B2))

    excel sumproduct1.PNG

    Then I have added a column (A) to include the name of the tab I want to lookup using my INDIRECT addition to the SUMPRODUCT

    =SUMPRODUCT((INDIRECT("'"&$A2&"'!AM7:AP224")),(INDIRECT("'"&$A2&"'!AM7:AM224"=B2)*(INDIRECT("'"&$A2&"'!AM7:AP7"=C1))))

    Here are the parameters

    1. Tab name (cell A2 in summary tab) - Property 1
    2. Array (cells AM7:AP224 under the Tab called Property 1 (A2 in summary tab))
    3. Criteria 1 range (cells AM7:AM224 under the Tab called Property 1 (A2 in summary tab)) – the month
    4. Criteria 1 (cell B18) – the month
    5. Criteria 2 range (cells AM7:AP7 under the Tab called Property 1 (A2 in summary tab)) – the category called ‘purchase’
    6. Criteria 2 (cell B18) – the category called ‘purchase’


    excel sumproduct2.PNG

    However its returning a #REF!

    Here is the array1 from 'Property 1' tab

    excel sumproduct.PNG

    Any help would be much appreciated, cheers.

  2. #2
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT using INDIRECT function

    No idea why the pics aren't showing up, anyone?

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: SUMPRODUCT using INDIRECT function

    dont use pics
    majority of users cannot view them
    attach a file instead
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT using INDIRECT function

    Tried to add attachment by clicking the paperclip but gave me no window... tried turning pop up blockers off, am I missing something? Nothing seems to be working on this maiden voyage of excelforum.com

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: SUMPRODUCT using INDIRECT function

    you dont need the '

    try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: SUMPRODUCT using INDIRECT function

    shrugs
    try other methods of uploading
    http://www.excelforum.com/the-water-...his-forum.html

  7. #7
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT using INDIRECT function

    Hi, it returns a #REF!, not sure if its because my array is so clunky, it contains multiple text headings (picture I table for January with another table directly below and so on) Ill try and type it below

    AM AN AO AP
    7. Purchases Sales Transfers
    8. 100.00
    9. 20.00
    . 20.00
    .
    10. January 100.00 40.00 0.00 Subtotal line
    .
    .
    .
    20. Purchases Sales Transfers
    21. 1.00
    20.00
    5.00 50.00
    .
    .
    25.February 26.00 0.00 50.00 Subtotal line


    So the array covers down to December, not sure if the text cells and the blank cells are causing the #REF! error??

  8. #8
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT using INDIRECT function

    Dam it, even my bl00dy post was reformatted to take any meaning away from it!!

  9. #9
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT using INDIRECT function

    Testing #1
    Attached Images Attached Images

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: SUMPRODUCT using INDIRECT function

    ok i lie, you do need ' because your sheet name contains space in it
    try this instead
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT using INDIRECT function

    Testing # 2
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: SUMPRODUCT using INDIRECT function

    see attached updated
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-06-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    46

    Re: SUMPRODUCT using INDIRECT function

    Yes! it worked, thanks mate... that was an ordeal

  14. #14
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: SUMPRODUCT using INDIRECT function

    no worries...yeah you need to be pretty specific when you comes to using indirect in sumproduct
    one wrong ( will give you error or worse yet the wrong answer!

  15. #15
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: SUMPRODUCT using INDIRECT function

    You can also try Offset function try

    C2=OFFSET(INDIRECT("'"&$A2&"'!AM6"),MATCH($B2,INDIRECT("'"&$A2&"'!AM8:AM174"),0)+1,MATCH(C$1,INDIRECT("'"&$A2&"'!AN7:AP7"),0),,) and drag towards the cells and down.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  16. #16
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: SUMPRODUCT using INDIRECT function

    if your not summing anything...and only need that singular line to return..which it looks like you do
    a straight up vlookup with indirect would work

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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 with SUMPRODUCT
    By vba_madness in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2015, 09:19 AM
  2. [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
  3. Replies: 6
    Last Post: 03-09-2011, 08:01 AM
  4. How To Use Indirect Function Within Sumproduct
    By modytrane in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-30-2009, 01:42 PM
  5. Can Indirect Function be used in Sumproduct?
    By all4excel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2008, 05:00 PM
  6. include INDIRECT function into SUMPRODUCT formula
    By markx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-09-2005, 01:10 PM
  7. Help with Sumproduct with Indirect
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 06:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1