# Thread: vlookup within sumif (2003)

1. ## 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
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)))

2. ## 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. ## Re: vlookup within sumif (2003)

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

Steve

4. ## Re: vlookup within sumif (2003)

If your table is always going to be:

1 AC
.
.
.
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. ## 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. ## 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.

Alex

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