Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup within sumif (2003)

    I need to update the formula below so as to tell it which column to sum based upon the return from cell C7, which in this case will be set to 1:

    =SUMIF('This Month EE Time'!$AB:$AB,$C$5&D$1&$B31,'This Month EE Time'!$AC:$AC)

    Currently the vlookup should return the AC from the following table


    1 AC
    2 AD
    3 AE


    I have tried using indirect as shown below but this did not work.

    =SUMIF('This Month EE Time'!$AB:$AB,$C$5&D$1&$B31,'This Month EE Time'!$indirect(Vlookup(C7,'Column Allocator'!A1:B3,2,false)):$indirect(Vlookup(C7,'C olumn Allocator'!A1:B3,2,false)))

    Can somebody please advise how I can do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup within sumif (2003)

    INDIRECT expects a string argument. If it consists of several parts, you must concatenate them using &, and literal parts must be enclosed in quotes:

    =SUMIF('This month EE Time'!$AB:$AB,$C$5&D$1&$B31,INDIRECT("'This Month EE Time'!$"&VLOOKUP(C7,'Column Allocator'!A1:B3,2,FALSE)&":$"&VLOOKUP(C7,'Column Allocator'!A1:B3,2,FALSE)))

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup within sumif (2003)

    I don't understand what you are trying to do. Could you elaborate?

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup within sumif (2003)

    If your table is always going to be:

    1 AC
    2 AD
    .
    .
    .
    10 AL
    etc.

    you could try combining the SUMIF and OFFSET function as follows:

    =SUMIF('This Month EE Time'!$AB:$AB,$C$5&D$1&$B31,OFFSET('This Month EE Time'!$AB:$AB,0,C7))

    The OFFSET function will return the array that starts 0 rows down and C7 columns to the right of column AB and is the same height (65536) and width (1) as column AB.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup within sumif (2003)

    I was just thinking that using the OFFSET function I described means that your formulas will still work even if you inserted or deleted columns to the left of column AB. If you use the INDIRECT function, you would have to change your table.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup within sumif (2003)

    Thanks

    I used the indirect version submitted and this has worked well. However I am still very hazy about how indirect works and why it needs &'s.

    Can anybody please clarify?

    Alex

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup within sumif (2003)

    The third argument in your SUMIF function is of the form

    <code>'This Month EE Time'!$AC:$AC</code>

    If you want to make this "variable" by using INDIRECT, you must use INDIRECT for the entire argument, not for individual parts of it. Basically (without making anything variable yet):

    <code>INDIRECT("'This Month EE Time'!$AC:$AC")</code>

    You want to replace the two occurrences of AC by a formula. To do this, you must split the expression

    <code>"'This Month EE Time'!$AC:$AC"</code>

    into parts:

    <code>"'This Month EE Time'!$" & "AC" & ":$" & "AC"</code>

    The & operator concatenates strings, that is, it "glues" bits of text together. I put in extra spaces around the & characters for readability, Excel will remove them. Next, we replace the literal values "AC" by the formula

    <code>VLOOKUP(C7,'Column Allocator'!A1:B3,2,FALSE)</code>

    so it becomes

    <code>"'This Month EE Time'!$" & VLOOKUP(C7,'Column Allocator'!A1:B3,2,FALSE) & ":$" & VLOOKUP(C7,'Column Allocator'!A1:B3,2,FALSE)</code>

    This has to be plugged into the INDIRECT function:

    <code>INDIRECT("'This Month EE Time'!$" & VLOOKUP(C7,'Column Allocator'!A1:B3,2,FALSE) & ":$" & VLOOKUP(C7,'Column Allocator'!A1:B3,2,FALSE))</code>

    and that becomes the third argument to SUMIF.

    Note: I think that the other solutions suggested in this thread (using OFFSET) are more efficient.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup within sumif (2003)

    Thanks Hans - that was a very concise explanation.

    Efficiency is becoming a major consideration as the functionality required grows, therefore I may change to using the offset function later. However this will require some re-writing in other areas

Posting Permissions

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