substituting a lookup ref. within a SUMIF()

keithkemble

Board Regular
Joined
Feb 15, 2002
Messages
160
This may be obvious but my excuse is I am still getting over my food poisoning:-

I have a lookup that returns the value K in cell DT8. (This value will change weekly)

I wish to use this value in place of the K$ in the following formula:-

SUMIF(A15:A2000,DR15,K$15:K$2000).

I am still waiting for my 3 books to arrive so I can find the appropriate action therefore any help would be most gratefull.
kk
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
On 2002-02-18 14:12, keithkemble wrote:
This may be obvious but my excuse is I am still getting over my food poisoning:-

I have a lookup that returns the value K in cell DT8. (This value will change weekly)

I wish to use this value in place of the K$ in the following formula:-

SUMIF(A15:A2000,DR15,K$15:K$2000).

I am still waiting for my 3 books to arrive so I can find the appropriate action therefore any help would be most gratefull.
kk

Try with

=SUMIF(A15:A2000,DR15,INDIRECT("K$15:K$" & DT8))
 
Upvote 0
On 2002-02-18 14:12, keithkemble wrote:
This may be obvious but my excuse is I am still getting over my food poisoning:-

I have a lookup that returns the value K in cell DT8. (This value will change weekly)

I wish to use this value in place of the K$ in the following formula:-

SUMIF(A15:A2000,DR15,K$15:K$2000).

I am still waiting for my 3 books to arrive so I can find the appropriate action therefore any help would be most gratefull.
kk

=SUMIF(A15:A2000,DR15,INDIRECT(DT8&"15:"&DT8&"2000"))
This message was edited by Mark W. on 2002-02-18 14:28
 
Upvote 0
No .

The letter K will change weekly.

i.e. next week it will chnage to L and so on.
Therefore the letter K almost become a variable.


To Juan P

I tried that reply but it throws up a #Ref

would there be 2
,Indirect("Dt8")&15:Indirect("DT8")&2000

This appears similar to the
Cell("contents",DT8) ref but that didn't work either.
 
Upvote 0
On 2002-02-18 14:36, keithkemble wrote:
No .

The letter K will change weekly.

i.e. next week it will chnage to L and so on.
Therefore the letter K almost become a variable.


To Juan P

I tried that reply but it throws up a #Ref

would there be 2
,Indirect("Dt8")&15:Indirect("DT8")&2000

This appears similar to the
Cell("contents",DT8) ref but that didn't work either.

Keith, look at my suggestion above...
 
Upvote 0
On 2002-02-18 14:36, keithkemble wrote:
No .

The letter K will change weekly.

i.e. next week it will chnage to L and so on.
Therefore the letter K almost become a variable.


To Juan P

I tried that reply but it throws up a #Ref

would there be 2
,Indirect("Dt8")&15:Indirect("DT8")&2000

This appears similar to the
Cell("contents",DT8) ref but that didn't work either.

I'll presume that DT8 holds some formula that computes where your dynamically changing range ends. If this assumption is right, I propose that you use in DT8 the following formula:

=MATCH(9.99999999999999E+307,K:K)

where I assume that K houses only the numbers of interest and nothing else.

You can rewrite the SUMIF formula as:


=SUMIF(OFFSET(A15,0,0,DT8,1),DR15,OFFSET(K15,0,0,DT8,1))

Aladin
This message was edited by Aladin Akyurek on 2002-02-18 14:44
 
Upvote 0
Mark,

You are a genius/
It works.
Those **** quotes again.
Got me at the week end as well.

Thanks to Juan P and Aladin
Very prompt response.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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
Back
Top