+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT and INDIRECT functions not working with a ROW reference when combined

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

    SUMPRODUCT and INDIRECT functions not working with a ROW reference when combined

    Sorry for the title! It's hard to describe exactly in such few words.

    Basically I have an equation:

    =SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

    It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

    My solution for the upper part of the fraction is this:
    "A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

    Where the user inputs the number of rows used in A8.

    The solution for the lower part of the fraction is this:
    "A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

    These two equations correctly mimic the behaviour of the original static one above and resolve correctly when combined: i.e. =A1/A2

    So, does anyone know why I can't combine them into a single equation? i.e.:
    "A4"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))/SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

    For some reason it just returns the value 0. No error, no #REF or whatnot. I can't see what I'm doing incorrectly!

    Any help or advice would be appreciated, thanks!

  2. #2
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: SUMPRODUCT and INDIRECT functions not working with a ROW reference when combined

    Ugh, I'm an idiot! For some reason the cell was formatted to be a number with 0 decimal places...

    Move along, nothing to see here!

    *Slaps forehead*

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: SUMPRODUCT and INDIRECT functions not working with a ROW reference when combined

    Happens !!
    Please do remember to mark the thread as [SOLVED].
    Cheers!

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: SUMPRODUCT and INDIRECT functions not working with a ROW reference when combined

    A sample workbook will be very much helpful to do the testing


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

+ 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] LEFT and SUMPRODUCT functions combined
    By DeZeeuw2 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-27-2014, 11:36 AM
  2. Use INDEX and INDIRECT functions combined.
    By maancalo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 02:43 PM
  3. [SOLVED] Indirect and Sumproduct functions?
    By steveexcels in forum Excel General
    Replies: 7
    Last Post: 05-25-2012, 11:23 AM
  4. SUMPRODUCT combined with other functions
    By tweety127 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2008, 12:30 PM
  5. 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