# Thread: SUMPRODUCT over variable range

1. I think I am having a brain fade at the moment and need to solve this problem quickly

I have a long formula in a cell on a spreadsheet which, for each row, multiplies a value by a rate found in another table. The rate is looked up based on a category in the original range :

=IF(H3 <> "",SUMPRODUCT(H6:H101*LOOKUP(\$E\$6:\$E\$101,Rates!\$B\$ 4:\$B\$7,INDEX(Rates!\$C\$4:\$M\$7,,MATCH(H3,Rates!\$C\$3: \$M\$3,0)))),"")

The problem is that the sumproduct ranges are not always fixed i.e. H6:H101 and E6:E101 are not always fixed - it depends how many lines of data the user fills in as input data. It could be H6:500 and E6:E500. My formula doesn't handle the fact that the range is variable. If the user only enters 101 rows of data, the 102nd row will not contain a category in column E, leading to a failed lookup on the rates, resulting in a #N/A sumproduct overall. I would like the formula just to ignore (or add zero to the sumproduct) if the column E value is empty.

By my reckoning, I could either:

1) Update the formula so it always does a sumproduct on the maximum range H6:H500 and E6:E500, however the lookup currently only works if the category in column E is not blank. If the value in column E is blank, the formula returns a #N/A. So if the user only inputs 101 rows, I need to change the formula so it ignores from 102 to 500 where column E is blank.

2) Dynamically build the sumproduct range in the formula using VB or some other method. I have already stored the row number of the last row that the user input in a cell, i.e. if the user input 101 rows, the cell contains the value 101, or if he input 500 rows, it contains the value 500. Perhaps there is some way I can modify the formula to avoid hardcoding the ranges.

Bit difficult to explain.

Cheers,
Dom

2. Donald,

What you need is dynamic named ranges:

=OFFSET(Sheet1!\$E\$6,0,0,COUNTA(Sheet1!\$E\$6:\$E\$1000 0),COUNTA(Sheet1!\$E6\$2:\$E\$10000))

Use this formula inside the Define Name box as the Refers to: value and give it a name. As long as there are less than 10,000 rows it will adjust automatically. Increase/Decrease the number {in both places} if your likely maximum number of rows is more or less. Create a named range like this for each of your ranges. Then just use the names in your worksheet formulas.

I have created my two named ranges for the grade and the number of hours in columns E and H respectively but I needed to amend your suggestion.
The reason is that the "dynamic range" definitions should only be 1 column wide in each case. I amended the range definitions to:

WBSEffortRange =OFFSET(WBS!\$E\$6,0,0,COUNTA(WBS!\$E\$6:\$E\$1000),1)

This works fine...

HOWEVER

Is there another way to solve the same problem? I have been asked by users to allow blank rows and for them just to be discounted. Is there a way to do the sumproduct/lookup/index/match differently so it doesn't mind if I don't have a grade in column E - so for the blank row the product is just zero? This would both enable blank lines to exist and would also allow me to do away with the "dynamic ranges" and just have a static maximum range for the sumproduct?

4. Is the WPID column always populated for each row?

5. nope, a blank row would be a completely blank row..

I was hoping you might be able to put some kind of IF function around the lookup to say "return 0 if you can't find the rate in the table, rather than a #N/A".

6. I'd define your ranges as:
WBS!\$E\$6:INDEX(WBS!\$E:\$E,LOOKUP("ZZZ",WBS!\$B:\$B,ROW(WBS!\$B:\$B)))

otherwise they will be incorrect using COUNTA if you have blank rows.

For the SUMPRODUCT, you'll have to include an ISNA() or ISNUMBER() check.

7. Hi.. could you give me a complete example? I'm not so hot on these monster formulae

8. Basically:

=IF(H3 <> "",SUMPRODUCT(H6:H101*IF(ISNA(lookup_formula),0,lo okup_formula)),"")

It may be possible to simplify things, but we'd need to see a workbook.

9. Hi Rory,
thanks for the info.

I modified my formula to be

which is essentially

=SUMPRODUCT(efforts*rates) where the rate is looked up in a table on the "Rates" sheet depending on the year (row 5) and the grade (column 5). If the Grade (column 5) is blank, I want that product/row to return 0 i.e. contribute nothing to the total SUMPRODUCT.

The formula I created now seems to return 0 for the whole SUMPRODUCT - i.e. the whole calculation results in 0.

I attach my workbook. I would like row 5 , cols H - BC to show the total cost of the column. Blank rows should be permissible (at the moment I have to have a grade in col E), so the named range definitions "WBSEffortRange" and "WBSGradeRange" will have to change.

Hope you can help!

cheers
Dom

10. You need to array-enter that (Ctrl+Shift+Enter) for it to work.

11. ok, done that.. makes no (noticeable) difference. The total for the column is still zero where it shouldn't be..

12. I'd have to guess you did something wrong then, as it works fine in your sample file. Does the formula appear in the formula bar as:

Note: you have to array-enter it into one cell, and then fill it across using the fill handle. See attached file.

13. Hi Rory,

thanks again.. I realised what was wrong last night when I was drifting off to sleep!

The formula is

There was also a mistake in my range definitions which also caused #N/A even with the formula above. I used the rather poor formula auditing functionality to suss this one out.

I just have to modify my range definitions to use the INDEX instead of the COUNTA to accommodate blank lines. In your formula you suggested

WBS!\$E\$6:INDEX(WBS!\$E:\$E,LOOKUP("ZZZ",WBS!\$B:\$B,RO W(WBS!\$B:\$B)))

Can you let me know what the "ZZZ" part is? I'm not used to using INDEX / LOOKUP formulae...

cheers! (then I'll let you be, I promise )

Dom

14. The ZZZ is just a character string that will be 'larger' (in alphabetical order terms) than any value in that column - it's just to make sure the LOOKUP returns the last value.

15. okie doke.. thanks.

I have a user defined function called "GetWBSEndRowNum()" which works fine and returns the last row entered by the user. Can I use this in the range definitions instead of this lookup/index method? I have tried a few things and i haven'T figured it out yet.

cheers
Dominic

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•