# How to combine a vlookup with a sumif function!!!

Mark the Shark
Guest
Posts: n/a

 6th Apr 2005
I've been trying to combine a vlookup function with a sumif function
for weeks now, however, without success.

This is my problem. Instead of looking up a single value, i.e.
=vlookup(A1, rang, 4, false), I like to do this for an entire range
and suming up that range. Something like this:
1 2 3 4 5 6
Table 1 10€ 35€ 50€ 23€ 60€ 50€
Jack 10€ 35€ 50€ 23€ 60€ 50€
Brian 10€ 35€ 50€ 23€ 60€ 50€
Juergen 10€ 35€ 50€ 23€ 60€ 50€
Feff 10€ 35€ 50€ 23€ 60€ 50€

So, I like to look-up the sum of these value without suming up these
values in table 1 and doing a vlookup on the sum.

Table 2

Feff
Juergen
Jack

I think the formula should look something like
this...=(sum(if(vlookup(Table2'A1;range;1;false)=Table2'A1;sum(range)))...I
really have no idea how excel can choose the particular row to sum up
the value. I hope this is not too confusing to you. I figured I'd
write it in English rather than German as more user can help me out on
this. I really appreciate any help. thanks you!!!

=?Utf-8?B?RHVrZSBDYXJleQ==?=
Guest
Posts: n/a

 6th Apr 2005
If you know how many columns wide the data is, then it a SUM will work for you

Let's assume row 1 has column headings and the data starts in row 2. Your

=SUM(OFFSET(B1:G1,MATCH("Jack",A2:A6,0),0,1,6))

"Mark the Shark" wrote:

> I've been trying to combine a vlookup function with a sumif function
> for weeks now, however, without success.
>
> This is my problem. Instead of looking up a single value, i.e.
> =vlookup(A1, rang, 4, false), I like to do this for an entire range
> and suming up that range. Something like this:
> 1 2 3 4 5 6
> Table 1 10â‚¬ 35â‚¬ 50â‚¬ 23â‚¬ 60â‚¬ 50â‚¬
> Jack 10â‚¬ 35â‚¬ 50â‚¬ 23â‚¬ 60â‚¬ 50â‚¬
> Brian 10â‚¬ 35â‚¬ 50â‚¬ 23â‚¬ 60â‚¬ 50â‚¬
> Juergen 10â‚¬ 35â‚¬ 50â‚¬ 23â‚¬ 60â‚¬ 50â‚¬
> Feff 10â‚¬ 35â‚¬ 50â‚¬ 23â‚¬ 60â‚¬ 50â‚¬
>
> So, I like to look-up the sum of these value without suming up these
> values in table 1 and doing a vlookup on the sum.
>
> Table 2
>
> Feff
> Juergen
> Jack
>
> I think the formula should look something like
> this...=(sum(if(vlookup(Table2'A1;range;1;false)=Table2'A1;sum(range)))...I
> really have no idea how excel can choose the particular row to sum up
> the value. I hope this is not too confusing to you. I figured I'd
> write it in English rather than German as more user can help me out on
> this. I really appreciate any help. thanks you!!!
>

Domenic
Guest
Posts: n/a

 6th Apr 2005
Also...

Table2!B1, copied down:

=SUM(INDEX(Table1!\$B\$1:\$G\$5,MATCH(A1,Table1!\$A\$1:\$A\$5,0),0))

Alternatively...

Table2!B1, copied down:

=SUM(VLOOKUP(A1,Table1!\$A\$1:\$G\$5,{2,3,4,5,6,7},0))

OR

=SUM(VLOOKUP(A1,Table1!\$A\$1:\$G\$5,ROW(INDIRECT("2:7")),0))

Both of these need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

In article <(E-Mail Removed)>,
http://www.pcreview.co.uk/forums/(E-Mail Removed) (Mark the Shark) wrote:

> I've been trying to combine a vlookup function with a sumif function
> for weeks now, however, without success.
>
> This is my problem. Instead of looking up a single value, i.e.
> =vlookup(A1, rang, 4, false), I like to do this for an entire range
> and suming up that range. Something like this:
> 1 2 3 4 5 6
> Table 1 10€ 35€ 50€ 23€ 60€ 50€
> Jack 10€ 35€ 50€ 23€ 60€ 50€
> Brian 10€ 35€ 50€ 23€ 60€ 50€
> Juergen 10€ 35€ 50€ 23€ 60€ 50€
> Feff 10€ 35€ 50€ 23€ 60€ 50€
>
> So, I like to look-up the sum of these value without suming up these
> values in table 1 and doing a vlookup on the sum.
>
> Table 2
>
> Feff
> Juergen
> Jack
>
> I think the formula should look something like
> this...=(sum(if(vlookup(Table2'A1;range;1;false)=Table2'A1;sum(range)))...I
> really have no idea how excel can choose the particular row to sum up
> the value. I hope this is not too confusing to you. I figured I'd
> write it in English rather than German as more user can help me out on
> this. I really appreciate any help. thanks you!!!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Amanda W. Microsoft Excel Worksheet Functions 3 9th Jun 2009 07:26 AM brandon Microsoft Excel Misc 2 17th Sep 2008 05:44 PM buffgirl71 Microsoft Excel Misc 12 15th Nov 2006 12:36 AM =?Utf-8?B?aG9iYmVzb24=?= Microsoft Excel Worksheet Functions 2 20th May 2005 01:26 AM =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM

Features