PC Review


Reply
Thread Tools Rate Thread

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!!!
 
Reply With Quote
 
 
 
 
=?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
example shows 1 column of names & 6 columns of data. How about this formula

=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!!!
>

 
Reply With Quote
 
 
 
 
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)>,
(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!!!

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I combine worksheets w/o enough rows to combine? Amanda W. Microsoft Excel Worksheet Functions 3 9th Jun 2009 07:26 AM
Combine cells with the same reference and combine quantities brandon Microsoft Excel Misc 2 17th Sep 2008 05:44 PM
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP buffgirl71 Microsoft Excel Misc 12 15th Nov 2006 12:36 AM
How can I combine the functions of a vlookup and sumif without a p =?Utf-8?B?aG9iYmVzb24=?= Microsoft Excel Worksheet Functions 2 20th May 2005 01:26 AM
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:06 AM.