# Sumproduct with multiple criteria & dynamic range

1. ## 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.  Register To Reply

2. ## Re: Sumproduct with multiple criteria & dynamic range

Try: ``Please Login or Register  to view this content.``  Register To Reply

3. ## 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.  Register To Reply

4. ## 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.``  Register To Reply

5. ## 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.  Register To Reply

6. ## Re: Sumproduct with multiple criteria & dynamic range Originally Posted by e_lad 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.`` Originally Posted by e_lad 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.  Register To Reply

7. ## 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.  Register To Reply

8. ## 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....  Register To Reply

9. ## 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.  Register To Reply

10. ## 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.  Register To Reply

11. ## Re: Sumproduct with multiple criteria & dynamic range

NBVC...got you. Thanks for all your help.  Register To Reply