Summing vlookups?

[Guest]

I'm stuck! Here's what I'm trying to do:

Vlookup($B$1,Named Range,$B$2,False)

This works, but there's more than one match for $B$1 in column A. I want to
sum the values in the column indicated in cell B2 if the data in column A
match the data in cell B1 --

Sumif(A:A,B1,[Column indicated in cell B2])

The problem is that the column with the values to be summed will change, so
a simple Sumif won't work. Thanks!

 

[Frank Kabel]

Hi
try something like
=SUMIF('sheet1'!A1:A100,B2,'sheet1'!B1:B100)

 

[Guest]

Thanks for the quick reply Frank, but the number in cell B2 is the column for
the Vlookup and it changes. The problem that I had with the simple SumIf is
that the column to sum will change.

Hi
try something like
=SUMIF('sheet1'!A1:A100,B2,'sheet1'!B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

I'm stuck! Here's what I'm trying to do:

Vlookup($B$1,Named Range,$B$2,False)

This works, but there's more than one match for $B$1 in column A. I want
to
sum the values in the column indicated in cell B2 if the data in column A
match the data in cell B1 --

Sumif(A:A,B1,[Column indicated in cell B2])

The problem is that the column with the values to be summed will change,
so
a simple Sumif won't work. Thanks!

 

[Guest]

You can still do it, assume the named range is MyTable, then use

=SUMPRODUCT(--(INDEX(MyTable,,1)=B1),INDEX(MyTable,,B2))

the first 1 in index tells it to find the match in the first column just
like in a vlookup, then B2 tells what column to sum where A is the lookup
value


Regards,

Peo Sjoblom

Thanks for the quick reply Frank, but the number in cell B2 is the column for
the Vlookup and it changes. The problem that I had with the simple SumIf is
that the column to sum will change.

Hi
try something like
=SUMIF('sheet1'!A1:A100,B2,'sheet1'!B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

I'm stuck! Here's what I'm trying to do:

Vlookup($B$1,Named Range,$B$2,False)

This works, but there's more than one match for $B$1 in column A. I want
to
sum the values in the column indicated in cell B2 if the data in column A
match the data in cell B1 --

Sumif(A:A,B1,[Column indicated in cell B2])

The problem is that the column with the values to be summed will change,
so
a simple Sumif won't work. Thanks!

 

[Guest]

Ah! That did the trick! Thanks so much!

You can still do it, assume the named range is MyTable, then use

=SUMPRODUCT(--(INDEX(MyTable,,1)=B1),INDEX(MyTable,,B2))

the first 1 in index tells it to find the match in the first column just
like in a vlookup, then B2 tells what column to sum where A is the lookup
value


Regards,

Peo Sjoblom

Thanks for the quick reply Frank, but the number in cell B2 is the column for
the Vlookup and it changes. The problem that I had with the simple SumIf is
that the column to sum will change.

Hi
try something like
=SUMIF('sheet1'!A1:A100,B2,'sheet1'!B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany
I'm stuck! Here's what I'm trying to do:

Vlookup($B$1,Named Range,$B$2,False)

This works, but there's more than one match for $B$1 in column A. I want
to
sum the values in the column indicated in cell B2 if the data in column A
match the data in cell B1 --

Sumif(A:A,B1,[Column indicated in cell B2])

The problem is that the column with the values to be summed will change,
so
a simple Sumif won't work. Thanks!