Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: substituting a lookup ref. within a SUMIF()

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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))
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    Do you mean by K$ where the range in K ends?

    Aladin

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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