Address Func & Sumproduct

sfb

Board Regular
Joined
Apr 30, 2004
Messages
67
I've searched far and wide on the board and am coming up empty. Can someone please help me figure out why the following won't work?

=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(("'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112))>0))

I'm getting a #VALUE! error

When I do this part by itself:

"'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112)

I get what I want:
'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175

and when I do a straight:
=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175)>0))

I get 8, which is what I want....

So, how come it won't work in the original (albeit messy) formula?

Ultimately, I need the column to be variable based on an index/match. So i was trying to use the address formula so I could then point the column (112 in this example) to another cell where I've done the index/match piece....

Thoughts?

SFB
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
in sumprdocut the arrays should be of the same size. your frist array is a5 to a175. in your first formula the are the other arrays of the same size i.e. 5 to 175 check t That is why when you do STRAIGHT it works. Your check the array where you use address (....).
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi SFB

Your formula cannot work because the second parameter is not an address but a text.

What you are doing is in a simple example the error in

=SUMPRODUCT(--(A1:A4=2),--("B1:B4">0))

As you see this cannot work and you'll get the #VALUE! error.

You must have an address and not text in the second parameter. You can use INDIRECT to convert text to address

=SUMPRODUCT(--(A1:A4=2),--(INDIRECT("B1:B4")>0))

will work and is equivalent to

=SUMPRODUCT(--(A1:A4=2),--(B1:B4>0))

So please try converting the text in the second parameter to address with INDIRECT.

Hope this helps
PGC
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I've searched far and wide on the board and am coming up empty. Can someone please help me figure out why the following won't work?

=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(("'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112))>0))

I'm getting a #VALUE! error

When I do this part by itself:

"'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,112) & ":" & ADDRESS(175,112)

I get what I want:
'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175

and when I do a straight:
=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$DH$5:$DH$175)>0))

I get 8, which is what I want....

So, how come it won't work in the original (albeit messy) formula?

Ultimately, I need the column to be variable based on an index/match. So i was trying to use the address formula so I could then point the column (112 in this example) to another cell where I've done the index/match piece....

Thoughts?

SFB

Invoking ADDRESS requires also INDIRECT, which would be troublesome if the target workbook is closed. Try to plug an Index/Match expression directly in the formula.
 

sfb

Board Regular
Joined
Apr 30, 2004
Messages
67

ADVERTISEMENT

Thanks to all of you for the responses!

venkat1926 - the ranges are the same... i made sure I confirmed that! :)

pgc01 - A-ha! Ok, that worked! thank you!

but... as Aladin said...
I have trouble if the target workbook is closed (#REF!). I can potentially work around that, but if it's possible, i'd rather do it without that volatility.

Aladin -
I'm afraid I'm stuck as to how to do the index/match without using address at all (which would throw me back into the indirect problem).

The current index/match formula I have gives me the column (out of a range of many columns) which I need to use to do the sumproduct.

=INDEX(MATCH(VLOOKUP(C7,'[XRef - Fee Table Names - Escher Names.xls]Sheet1'!$B$1:$C$58,2,FALSE),'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$3:$EZ$3,0),1,1)

in the above example, column 112. I don't know how to convert that to $DH$5:$DH$175 without using the address function. I'll take any guidance you can provide!!

CUrrently, inserting that index/match into the formula directly, just gives me a messier address function for which I still need to use Indirect...

=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(INDIRECT("'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,INDEX(MATCH(VLOOKUP(C7,'[XRef - Fee Table Names - Escher Names.xls]Sheet1'!$B$1:$C$58,2,FALSE),'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$3:$EZ$3,0),1,1)) & ":" & ADDRESS(175,INDEX(MATCH(VLOOKUP(C7,'[XRef - Fee Table Names - Escher Names.xls]Sheet1'!$B$1:$C$58,2,FALSE),'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$3:$EZ$3,0),1,1)))>0))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Thanks to all of you for the responses!

venkat1926 - the ranges are the same... i made sure I confirmed that! :)

pgc01 - A-ha! Ok, that worked! thank you!

but... as Aladin said...
I have trouble if the target workbook is closed (#REF!). I can potentially work around that, but if it's possible, i'd rather do it without that volatility.

Aladin -
I'm afraid I'm stuck as to how to do the index/match without using address at all (which would throw me back into the indirect problem).

The current index/match formula I have gives me the column (out of a range of many columns) which I need to use to do the sumproduct.

=INDEX(MATCH(VLOOKUP(C7,'[XRef - Fee Table Names - Escher Names.xls]Sheet1'!$B$1:$C$58,2,FALSE),'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$3:$EZ$3,0),1,1)

in the above example, column 112. I don't know how to convert that to $DH$5:$DH$175 without using the address function. I'll take any guidance you can provide!!

CUrrently, inserting that index/match into the formula directly, just gives me a messier address function for which I still need to use Indirect...

=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(INDIRECT("'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!" & ADDRESS(5,INDEX(MATCH(VLOOKUP(C7,'[XRef - Fee Table Names - Escher Names.xls]Sheet1'!$B$1:$C$58,2,FALSE),'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$3:$EZ$3,0),1,1)) & ":" & ADDRESS(175,INDEX(MATCH(VLOOKUP(C7,'[XRef - Fee Table Names - Escher Names.xls]Sheet1'!$B$1:$C$58,2,FALSE),'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$3:$EZ$3,0),1,1)))>0))

=SUMPRODUCT(--('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$A$175>0),--(INDEX('[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$5:$EZ$175,0,MATCH(VLOOKUP(C7,[Names.xls]Sheet1!$B$1:$C$58,2,0),'[1Q06_AvgFee per Client per Fund per Quarter_FINAL_SM.xls]DB_CURRENT CleanNames wTiers'!$A$3:$EZ$3,0))>0))
 

sfb

Board Regular
Joined
Apr 30, 2004
Messages
67
:oops:
I am not worthy.

You, sir, are a genius.

Thank you so much. I spent a good portion of three days trying to work that through... and it's SO simple!!! *sigh*

Thank you, truly.

SFB
 

Forum statistics

Threads
1,137,294
Messages
5,680,660
Members
419,923
Latest member
Kalthus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top