Hello,

I am trying to figure out a way to get around this type of formula:

=vlookup(3,dollars,2,false)+vlookup(5,dollars,fals e)


here is a sample of the dollars range:

Terr# dollars
1 100
2 75
3 150
4 300
5 85

What I would really like is this to work with one formula instead of adding
formulas as above.....something like:

=vlookup({3,5},dollars,2,false)

This only returns the first instance of terr# 3 ( 150 )....when I want to
add terr#'s 3 and 5 ( or more in my actual scenario )

Please help and THANK YOU!

=SUMPRODUCT(dollars*(ISNUMBER(MATCH(terr,{3,5},0)) ))

=SUMPRODUCT(B2:B6*(ISNUMBER(MATCH(A2:A6,{3,5},0))) )

HTH
Kostis Vezerides

On Jan 26, 6:41 pm, fomo wrote:
Hello,

I am trying to figure out a way to get around this type of formula:

=vlookup(3,dollars,2,false)+vlookup(5,dollars,fals e)

here is a sample of the dollars range:

Terr# dollars
1 100
2 75
3 150
4 300
5 85

What I would really like is this to work with one formula instead of adding
formulas as above.....something like:

=vlookup({3,5},dollars,2,false)

This only returns the first instance of terr# 3 ( 150 )....when I want to
add terr#'s 3 and 5 ( or more in my actual scenario )

Please help and THANK YOU!

Thank you...this works well for the two column table below. However, I am
using a number named ranges containing tables of data with 20+ columns. This
is why I am using vlookup.

In one cell I need to sum 13 items from one of the columns in my spreadsheet
using 13 territory numbers specified in either another range or int he
formula itself......like:

=vlookup(terrs,lookuptable,4,false) or

=vlookup({13,15,17,18},lookuptable,4,false)

or am I simply not understanding the versatility of the samples you provided
below?


"vezerid" wrote:

=SUMPRODUCT(dollars*(ISNUMBER(MATCH(terr,{3,5},0)) ))

=SUMPRODUCT(B2:B6*(ISNUMBER(MATCH(A2:A6,{3,5},0))) )

HTH
Kostis Vezerides

On Jan 26, 6:41 pm, fomo wrote:
Hello,

I am trying to figure out a way to get around this type of formula:

=vlookup(3,dollars,2,false)+vlookup(5,dollars,fals e)

here is a sample of the dollars range:

Terr# dollars
1 100
2 75
3 150
4 300
5 85

What I would really like is this to work with one formula instead of adding
formulas as above.....something like:

=vlookup({3,5},dollars,2,false)

This only returns the first instance of terr# 3 ( 150 )....when I want to
add terr#'s 3 and 5 ( or more in my actual scenario )

Please help and THANK YOU!

Here is a way to sum 13 rows, or even 30 rows
( the limit of arguments in a function) without much typing.
You can specify the desired rows in a range named TerrSet.
A single formula using Sumproduct is used.
Assume your data and headers look like this:

Amt 1 2 3 4 5
1 38 34 70 95 28
2 55 72 47 19 83
3 79 58 37 41 95
4 13 82 31 16 85
5 78 63 86 50 68
6 77 47 52 38 88
7 50 27 14 44 26
8 81 64 60 14 96
9 65 13 53 38 50
10 84 92 80 25 30
11 10 38 68 60 48
12 70 76 66 99 74
13 91 16 18 70 50
14 43 58 81 13 36
15 53 51 98 13 62
TerrT

TerrSet
7
9
11
15

Name the 1 x 5 header Amt
Name the 15 x 1 header TerrT
Name the 15 x 5 data array ArrayA
Name the 4 x 1 set of desired rows TerrSet
Assuming you want to look in column 3 and
sum rows 7, 9, 11 and 15 of that column:
=SUMPRODUCT(arrayA*COUNTIF(TerrSet,TerrT)*(Amt=3))
=233

Array enter (i.e., enter with Ctrl+Shift+Enter instead of just Enter)
the formula into a two- (or more) column row; or, if you use a semicolon
instead of a comma to delimit the lookup values, array enter it into a
two- (or more) row column.

Alan Beban

fomo wrote:
Hello,

I am trying to figure out a way to get around this type of formula:

=vlookup(3,dollars,2,false)+vlookup(5,dollars,fals e)


here is a sample of the dollars range:

Terr# dollars
1 100
2 75
3 150
4 300
5 85

What I would really like is this to work with one formula instead of adding
formulas as above.....something like:

=vlookup({3,5},dollars,2,false)

This only returns the first instance of terr# 3 ( 150 )....when I want to
add terr#'s 3 and 5 ( or more in my actual scenario )

Please help and THANK YOU!

Alan Beban <unavailable wrote...
Array enter (i.e., enter with Ctrl+Shift+Enter instead of just Enter)
the formula into a two- (or more) column row; or, if you use a semicolon
instead of a comma to delimit the lookup values, array enter it into a
two- (or more) row column.
....

Yes, but the OP wants to sum the results, so not unlikely the OP wants
one formula in one cell. Simpler to use

=SUMPRODUCT((INDEX(Dollars,0,1)={3,5,...})*INDEX(D ollars,0,2))

Taking Fomo's second post as a reference, I assume that your range
"Dollars"
refers to a 20+ column by 13+ row array.
A named set of Territory numbers (13+) is on the left side.
I don't see any reference to Territory in your formula.
The OP wants to select a subset of 4 territories.
Assuming you meant "Territory" instead of "Dollars"
INDEX(Territory,0,1)={3,5,7,9} does not work since the ranges are not
equal.
Besides, the OP wants the subset of territories in a separately named
range,
presumably not padded with 0s.

"Herbert Seidenberg" wrote...
Taking Fomo's second post as a reference, I assume that your range
"Dollars" refers to a 20+ column by 13+ row array.

No idea. And since I wasn't responding to you, don't much care what
transpired in the other branch of this discussion thread.

I was referring to the OP's original formulas, which I didn't quote
but which Alan Beban did, namely

..=vlookup(3,dollars,2,false)+vlookup(5,dollars, false)

which is an error since the second VLOOKUP call lacks 4 arguments, but
it shows the OP wants to sum values from different rows in a range
named dollars, and

..=vlookup({3,5},dollars,2,false)

which makes it clearer that the OP wants values from column 2 of
dollars.

If you want to roll the other branch into this one, feel free.

A named set of Territory numbers (13+) is on the left side.
I don't see any reference to Territory in your formula.

If you want to add one, feel free. Note that the OP's follow-up to
which you responded included the statement

..using 13 territory numbers specified in either another range or int he
..formula itself......like:
..
..=vlookup(terrs,lookuptable,4,false) or
..
..=vlookup({13,15,17,18},lookuptable,4,false)

So the OP seems to be OK hardcoding the territory numbers. Maybe not
best practice, but feel free to modify my formulas yourself. Anyway,
(1) using the name terrs appeared in a different branch, so not
necessarily relevant in the branch in which I was responding, and (2)
the OP seems not to *require* it anyway.

The OP wants to select a subset of 4 territories.
Assuming you meant "Territory" instead of "Dollars"
INDEX(Territory,0,1)={3,5,7,9} does not work since the ranges are not
equal.

You're failing to distinguish between the setup you used in your
formula, in which you assumed a multiple-row, single-column range
named TerrSet and the array constant I used, which was SINGLE-ROW,
MULTIPLE-COLUMN. Orientation makes all the difference when you pay
enough attention to spot it.

You obviously didn't bother to test my formula. Calling INDEX with 2nd
argument 0 returns all rows in the specified column, so effectively a
multiple-row by single-column array. OTOH, {3,5,7,9} is a single-row
by multiple-column array. You seem ignorant of the fact that when
Excel performs arithmetic on an M-by-1 array and a 1-by-N array, it
produces an M-by-N array. For example,

{1;10;100;1000}*{1,2,3} = {1,2,3;10,20,30;100,200,300;1000,2000,3000}

that is, {1;10;100;1000} is treated like
{1,1,1;10,10,10;100,100,100;1000,1000,1000} and {1,2,3} is treated
like {1,2,3;1,2,3;1,2,3;1,2,3}. Try stepwise evaluatation of my
formula to see how it works. You might learn something.

Besides, the OP wants the subset of territories in a separately named
range, presumably not padded with 0s.

Didn't seem to be a requirement, just an alternative. Still, if the OP
has one range named dollars with 1st column containing territory
codes, and another range named terrs containing just the territory
codes s/he wants to select, then another alternative would be

=SUMPRODUCT((COUNTIF(terrs,INDEX(dollars,0,1)))*IN DEX(dollars,0,4))

in which dollars refers to something like the combination of your
TerrT and ArrayA ranges and terrs refers to something like your
TerrSet range. I'll leave it up to you to figure out how
COUNTIF(terrs,INDEX(dollars,0,1)) works.