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

Discussion in 'Microsoft Excel Misc' started by Mark the Shark, Apr 6, 2005.

1. ### Mark the SharkGuest

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

Mark the Shark, Apr 6, 2005

2. ### GuestGuest

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

Guest, Apr 6, 2005

3. ### DomenicGuest

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 <>,
(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, Apr 6, 2005