+ Reply to Thread
Results 1 to 11 of 11

Sumproduct with multiple criteria & dynamic range

  1. #1
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Sumproduct with multiple criteria & dynamic range

    Hi, I am trying to use SUMPRODUCT function to sum with multiple criteria with dynamic range so that the total sum is correctly calculated when a new line is inserted. I have attached the sample spreadsheet for you information.

    I would like the SUMPRODUCT range to include for the new line when a new row is inserted at the bottom of the table (i.e. Row 11). How can I ensure the SUMPRODUCT formula is automatically adjusted to cover the range. I seem to remember that this can somehow be accomplished with a dynamic name range with the combination of OFFSET function.

    Please I would appreciate your help.
    Attached Files Attached Files
    Last edited by e_lad; 03-17-2010 at 09:18 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sumproduct with multiple criteria & dynamic range

    Try:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Sumproduct with multiple criteria & dynamic range

    Hi NBVC, thanks for your input.

    However, the formula suggested has got the following problems:

    ■ Any data inserted below the table is reflected in the SUMPRODUCT formula

    ■ Any new row inserted at the top of the data within the table (i.e. Row 4) will not be taken into account by the SUMPRODUCT formula (sorry, I don't think I stated this requirement as well in my previous post).

    In summary, I want to be able to insert new rows in particular at the top and bottom of the data within the table (i.e. either at Row 4 or Row12) that the SUMPRODUCT formula will adjust the range itself to include the new rows inserted.

    Thanks in advance.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sumproduct with multiple criteria & dynamic range

    Data inserted below the table should not be reflected in the data with that formula...I tested it.

    This formula will allow you to insert row anywhere between row 3 and row 13 and will adjust the results accordingly.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Sumproduct with multiple criteria & dynamic range

    NBVC, that's great.

    I really want to understand how the formula works so that I can learn to customise next time round. Which part of the formula tells it to stop the calculation at the row just above the cell of the formula?

    The only other thing is....can you possibly make the formula go one step further to make provision for the table to move. Eg. when I insert rows at the top of the table i.e. above Row 3, it doesn't generate the #VALUE error message.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sumproduct with multiple criteria & dynamic range

    Quote Originally Posted by e_lad View Post
    The only other thing is....can you possibly make the formula go one step further to make provision for the table to move. Eg. when I insert rows at the top of the table i.e. above Row 3, it doesn't generate the #VALUE error message.
    Revise formula to:

    Please Login or Register  to view this content.
    Quote Originally Posted by e_lad View Post
    NBVC, that's great.

    I really want to understand how the formula works so that I can learn to customise next time round. Which part of the formula tells it to stop the calculation at the row just above the cell of the formula?
    The range to look in is determined by:

    INDEX(C:C,MATCH("Day",A:A,0)+1):INDEX(C:C,ROW()-1)

    This indexes a range between the first row after "Day" is found in column A to the row before the current row (the row the formula is in).

    Syntax of Index: INDEX(Array, Row_Num,[Col_Num]) where Col_num is optional and required only if Array is 2 dimensional. When nested in a formula it returns the cell reference at the intersect of Row_Num,Col_Num within Array... when used on its own, returns a value at the cell intersecting at Row_Num and Col_Num.


    So:

    INDEX(C:C,MATCH("Day",A:A,0)+1)

    says find the position (or row number) matching the word "Day" in column A, and add 1 to it... then index that in column C.... so as it is now, it would return C4.

    INDEX(C:C,ROW()-1)

    says index column C with the row number previous to the current row. So it would return C12.

  7. #7
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Sumproduct with multiple criteria & dynamic range

    Thanks for taking the time to explain.

    For some reason, when I insert the row(s) above Row 3, I still get the '#VALUE# error message.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sumproduct with multiple criteria & dynamic range

    Here is your sample book with my formula applied..

    I can insert rows above 3 without incident, I can insert rows between the blue areas without incident and sums according to changes....
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Sumproduct with multiple criteria & dynamic range

    Sorry, it was me who made a mistake in the formula.

    I still don't quite understand this part of the formula. Why would it be returning cell C12? How does it know to stop summing the range up to a row above Row 11 (the Total row)

    INDEX(C:C,ROW()-1)

    says index column C with the row number previous to the current row. So it would return C12.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sumproduct with multiple criteria & dynamic range

    This function: ROW()

    returns the row number the formula is in...

    so ROW()-1 is the row before the one you are in....

    In the case of your sample Row() returns 11.. so Row()-1 is 10... so formula indexes C10... not C12.. sorry I was testing on your sheet and had added a row or two.

    Try the Evaluate Formula tool in the Tools|Formula Auditing menu... keep clicking Evaluate to see how the formula evaluates itself in steps.
    Last edited by NBVC; 03-16-2010 at 02:14 PM.

  11. #11
    Registered User
    Join Date
    03-02-2010
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Sumproduct with multiple criteria & dynamic range

    NBVC...got you. Thanks for all your help.

+ 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