+ Reply to Thread
Results 1 to 11 of 11

Sumproduct, sumif and indirect functions not working

  1. #1
    Registered User
    Join Date
    11-13-2016
    Location
    Karachi, Pakistan
    MS-Off Ver
    2010
    Posts
    12

    Sumproduct, sumif and indirect functions not working

    Dear Friends,
    I am trying to track monthly sales orders, executions and returns with percentages. I am trying to apply the following formula but getting #ref error. I've attached the file, please review and point out what is wrong with this function. Thanks in advance.

    =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$5:$H$16&"'!B5:B29"),$B5,INDIRECT("'"&$H$5:$H$16&"'!C5:29")))

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Sumproduct, sumif and indirect functions not working

    Maybe like this, for Order:

    =IF($H5="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$H5&"'!B5:BC29"),$B5,INDIRECT("'"&$H5&"'!C5:C29"))))

    For Execution:

    =IF($H5="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$H5&"'!B5:B29"),$B5,INDIRECT("'"&$H5&"'!D5:D29"))))

    For Return:

    =IF($H5="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$H5&"'!B5:B29"),$B5,INDIRECT("'"&$H5&"'!E5:E29"))))

    put in first row in each category and copied down as necessary

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

    Re: Sumproduct, sumif and indirect functions not working

    Try

    C5=SUMPRODUCT(SUMIFS(INDIRECT("'"&H5:H14&"'!C:C"),INDIRECT("'"&H5:H14&"'!B:B"),B5)) and drag down!!!

    Change only highlighted part into D:D for Execution and E:E for return.
    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)

  4. #4
    Registered User
    Join Date
    11-13-2016
    Location
    Karachi, Pakistan
    MS-Off Ver
    2010
    Posts
    12

    Re: Sumproduct, sumif and indirect functions not working

    Thanks for the help buddy, but this function only showing the results from one sheet only. I want the sum of all sheets which are there for the whole month. Sum of orders, executions and returns for each sales representative from multiple sheets. I've listed all the sheet names beside the table.
    Last edited by stillyours786; 12-28-2016 at 03:58 AM.

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

    Re: Sumproduct, sumif and indirect functions not working

    Quote Originally Posted by stillyours786 View Post
    Thanks for the help buddy, but this function only showing the results from one sheet only. I want the sum of all sheets which are there for the whole monthly. Sum of orders, executions and returns for each sales representative from multiple sheets. I've listed all the sheet names beside the table.
    Did u check post #3???

  6. #6
    Registered User
    Join Date
    11-13-2016
    Location
    Karachi, Pakistan
    MS-Off Ver
    2010
    Posts
    12

    Re: Sumproduct, sumif and indirect functions not working

    Yes buddy it is working fine, but one more thing if i include more cells in the sheet list and leave it empty it is again returning #ref error, which will force me to add cell every time I add a new sheet. Is there any possible way that I don't have to edit function each time.

  7. #7
    Registered User
    Join Date
    12-09-2016
    Location
    Pune
    MS-Off Ver
    Windows 10
    Posts
    2

    Re: Sumproduct, sumif and indirect functions not working

    Hi,
    Please apply the below formula .I have tried to rework the formula and as you have written sheet name in H5 as 15-12-16 therefore ,it is picking the values from this sheet and further for B6 name it is picking the value from 16-12-16 sheet.
    Thanks

    =SUMPRODUCT(IFERROR((SUMIF(INDIRECT("'"&H:H&"'!B:B"),$B5,INDIRECT("'"&H:H&"'!C:C"))),0))

  8. #8
    Registered User
    Join Date
    11-13-2016
    Location
    Karachi, Pakistan
    MS-Off Ver
    2010
    Posts
    12

    Re: Sumproduct, sumif and indirect functions not working

    Hello cwaanupam,
    Thats not what the desired result. Last function posted by shukla is for fine for me but it not accepting empty range included in the function, it returns #ref error. I want some empty cells in the column so that when I include a new sheet, I just have to type a name for the new sheet and not edit all the functions on the DSRwiseSales sheet. Hope that briefs my concern well.
    Thanks

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

    Re: Sumproduct, sumif and indirect functions not working

    Quote Originally Posted by stillyours786 View Post
    Yes buddy it is working fine, but one more thing if i include more cells in the sheet list and leave it empty it is again returning #ref error, which will force me to add cell every time I add a new sheet. Is there any possible way that I don't have to edit function each time.
    Hey I have made sheet more efficient for you.

    I have made auto update sheet list with hyperlink putted formula in the sheet now whenever you will add or dell sheet you will just need to click on update sheet name tab and the formula will work fine.

    Check the attached file.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Sumproduct, sumif and indirect functions not working

    Try

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"),INDIRECT("'"&Sheets&"'!B:B"),$B5))

    Sheets is a named range with your tab names (I used column J)

    set "Refers to:" =OFFSET(DSRwiseSales!$J$2,,,COUNTA(DSRwiseSales!$J:$J)-1)

  11. #11
    Registered User
    Join Date
    11-13-2016
    Location
    Karachi, Pakistan
    MS-Off Ver
    2010
    Posts
    12

    Re: Sumproduct, sumif and indirect functions not working

    Thanks a lot guys... That will help. Stay blessed.

+ 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. [SOLVED] Sumproduct-Sumif-Indirect Formula Problem
    By PJH008 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-08-2016, 04:52 AM
  2. 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
  3. [SOLVED] SUMPRODUCT, SUMIF, INDIRECT and ADDRESS
    By dma1976 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2015, 10:11 AM
  4. [SOLVED] SUMPRODUCT and INDIRECT functions not working with a ROW reference when combined
    By Duoae in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2014, 03:28 AM
  5. [SOLVED] Indirect and Sumproduct functions?
    By steveexcels in forum Excel General
    Replies: 7
    Last Post: 05-25-2012, 11:23 AM
  6. Sumproduct(sumif(indirect
    By jmcgallan in forum Excel General
    Replies: 6
    Last Post: 09-17-2010, 12:03 PM
  7. Sumproduct & Indirect Functions
    By VBA Noob in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-16-2006, 04:13 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