Results 1 to 8 of 8
Thread: vlookup within sumif (2003)

20040818, 14:45 #1
 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?

20040818, 15:23 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)))

20040818, 15:30 #3
 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

20040819, 03:43 #4
 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.

20040819, 12:42 #5
 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.

20040820, 08:21 #6
 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

20040820, 08:42 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20040820, 08:54 #8
 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 rewriting in other areas