Results 1 to 15 of 25
Thread: SUMPRODUCT over variable range

20101108, 05:52 #1
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
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

20101108, 07:12 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,564
 Thanks
 384
 Thanked 1,480 Times in 1,346 Posts
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.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20101108, 07:53 #3
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
thanks for the reply..
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)
WBSGradeRange =OFFSET(WBS!$H$6,0,0,COUNTA(WBS!$H$6:$H$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?

20101108, 09:17 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 Posts
Is the WPID column always populated for each row?
Regards,
Rory
Microsoft MVP  Excel

20101108, 09:45 #5
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
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".

20101108, 10:49 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 Posts
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.Regards,
Rory
Microsoft MVP  Excel

20101108, 11:46 #7
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
Hi.. could you give me a complete example? I'm not so hot on these monster formulae

20101109, 07:24 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 Posts
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.Regards,
Rory
Microsoft MVP  Excel

20101110, 02:50 #9
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
Hi Rory,
thanks for the info.
I modified my formula to be
=SUMPRODUCT(WBSEffortRange*IF(ISNUMBER(LOOKUP(WBSG radeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,M ATCH(H3,Rates!$C$3:$M$3,0)))),LOOKUP(WBSGradeRange ,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,MATCH(H3,R ates!$C$3:$M$3,0))),0))
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

20101110, 05:27 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 Posts
You need to arrayenter that (Ctrl+Shift+Enter) for it to work.
Regards,
Rory
Microsoft MVP  Excel

20101110, 05:59 #11
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
ok, done that.. makes no (noticeable) difference. The total for the column is still zero where it shouldn't be..

20101110, 08:32 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 Posts
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:
{=SUMPRODUCT(WBSEffortRange*IF(ISNUMBER(LOOKUP(WBSG radeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,M ATCH(H3,Rates!$C$3:$M$3,0)))),LOOKUP(WBSGradeRange ,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,MATCH(H3,R ates!$C$3:$M$3,0))),0))}
Note: you have to arrayenter it into one cell, and then fill it across using the fill handle. See attached file.Regards,
Rory
Microsoft MVP  Excel

20101111, 07:53 #13
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
Hi Rory,
thanks again.. I realised what was wrong last night when I was drifting off to sleep!
The formula is
=IF(K$3<>"",SUMPRODUCT(WBSEffortRange*IF(ISNA(LOOK UP(WBSGradeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4: $M$7,,MATCH(K3,Rates!$C$3:$M$3,0)))),0,LOOKUP(WBSG radeRange,Rates!$B$4:$B$7,INDEX(Rates!$C$4:$M$7,,M ATCH(K3,Rates!$C$3:$M$3,0))))),"")
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

20101111, 08:06 #14
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 Posts
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.
Regards,
Rory
Microsoft MVP  Excel

20101111, 08:17 #15
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
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