a twist on a VLOOKUP?
 

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

Sumif() will do what you want.

=SUMIF(Sheet3!$B$8:$B$15,Sheet1!B9,Sheet3!$F$8:$F$ 15)

Parameter 1 of 3 The range containing your lookup values
Parameter 2 of 3 The Lookup Value of Interest
Parameter 1 of 3 The Column to Sum

HTH


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

The limitation with the below formula is that it looks as though you are
having to tell it a specific column to sum. I will not know which column the
values are in. I only know that they are 4 columns to the right of the code
I am looking for. The code could be in any number of columns.

I do appreciate your help.
--
Valerie


"Jim May" wrote:

Sumif() will do what you want.

=SUMIF(Sheet3!$B$8:$B$15,Sheet1!B9,Sheet3!$F$8:$F$ 15)

Parameter 1 of 3 The range containing your lookup values
Parameter 2 of 3 The Lookup Value of Interest
Parameter 1 of 3 The Column to Sum

HTH


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

Thank you so much for your help. This looks as though it will do the trick.
--
Valerie


"Elkar" wrote:

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

No need for the unary minuses in this case


--
Regards,

Peo Sjoblom


"Elkar" wrote in message
...
I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and
then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search
the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

Sorry, I missed that portion of your requirements,,,
Looks like You got your answer all the same.

Hooray, hooray !!


"Valerie" wrote:

The limitation with the below formula is that it looks as though you are
having to tell it a specific column to sum. I will not know which column the
values are in. I only know that they are 4 columns to the right of the code
I am looking for. The code could be in any number of columns.

I do appreciate your help.
--
Valerie


"Jim May" wrote:

Sumif() will do what you want.

=SUMIF(Sheet3!$B$8:$B$15,Sheet1!B9,Sheet3!$F$8:$F$ 15)

Parameter 1 of 3 The range containing your lookup values
Parameter 2 of 3 The Lookup Value of Interest
Parameter 1 of 3 The Column to Sum

HTH


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

This did not work for me. Perhaps if I can understand a few things I could
adjust it to work. Why SUMPRODUCT since I do not need the product of
anything? What does the * do? What does the -- do?
--
Valerie


"Valerie" wrote:

Thank you so much for your help. This looks as though it will do the trick.
--
Valerie


"Elkar" wrote:

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

As Peo mentioned, the -- is not necessary. That was left over from my
original thought, which I forgot to remove. It really should have no effect
on the formula though.

The use of the SUMPRODUCT function creates an array.

Basically, think of it as each cell in your range is evaluated
independently, then the results are all added together at the end. So, in my
example, we start with cell A1. This is checked to see if it equals the code
123. If yes, it returns TRUE which has a value of 1. If no, it returns
FALSE which has a value of 0. The * symbol then multiplies the 1 or 0 by the
value found 4 cells to the right, in this case E1. Since anything multiplied
by 0 is 0, all false conditions are effectively eliminated from the final
sum. This then continues on for each cell in the range.

Since its still not working, perhaps a better explanation of what you want
along with some examples might help.

HTH,
Elkar

"Valerie" wrote:

This did not work for me. Perhaps if I can understand a few things I could
adjust it to work. Why SUMPRODUCT since I do not need the product of
anything? What does the * do? What does the -- do?
--
Valerie


"Valerie" wrote:

Thank you so much for your help. This looks as though it will do the trick.
--
Valerie


"Elkar" wrote:

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

The formula that you so kindly provided seems to be exactly right, however I
get a #value! error. This is supposed to indicate that the array argument
does not have the same dimensions. Does that refer to the range? I did use
the same cell range in both sections of the formula.
--
Valerie


"Elkar" wrote:

As Peo mentioned, the -- is not necessary. That was left over from my
original thought, which I forgot to remove. It really should have no effect
on the formula though.

The use of the SUMPRODUCT function creates an array.

Basically, think of it as each cell in your range is evaluated
independently, then the results are all added together at the end. So, in my
example, we start with cell A1. This is checked to see if it equals the code
123. If yes, it returns TRUE which has a value of 1. If no, it returns
FALSE which has a value of 0. The * symbol then multiplies the 1 or 0 by the
value found 4 cells to the right, in this case E1. Since anything multiplied
by 0 is 0, all false conditions are effectively eliminated from the final
sum. This then continues on for each cell in the range.

Since its still not working, perhaps a better explanation of what you want
along with some examples might help.

HTH,
Elkar

"Valerie" wrote:

This did not work for me. Perhaps if I can understand a few things I could
adjust it to work. Why SUMPRODUCT since I do not need the product of
anything? What does the * do? What does the -- do?
--
Valerie


"Valerie" wrote:

Thank you so much for your help. This looks as though it will do the trick.
--
Valerie


"Elkar" wrote:

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

The #VALUE! error could also occur if one of the numbers being summed is not
a number. Do you have text values that would be 4 columns to the right of
any of the codes you're looking up?


"Valerie" wrote:

The formula that you so kindly provided seems to be exactly right, however I
get a #value! error. This is supposed to indicate that the array argument
does not have the same dimensions. Does that refer to the range? I did use
the same cell range in both sections of the formula.
--
Valerie


"Elkar" wrote:

As Peo mentioned, the -- is not necessary. That was left over from my
original thought, which I forgot to remove. It really should have no effect
on the formula though.

The use of the SUMPRODUCT function creates an array.

Basically, think of it as each cell in your range is evaluated
independently, then the results are all added together at the end. So, in my
example, we start with cell A1. This is checked to see if it equals the code
123. If yes, it returns TRUE which has a value of 1. If no, it returns
FALSE which has a value of 0. The * symbol then multiplies the 1 or 0 by the
value found 4 cells to the right, in this case E1. Since anything multiplied
by 0 is 0, all false conditions are effectively eliminated from the final
sum. This then continues on for each cell in the range.

Since its still not working, perhaps a better explanation of what you want
along with some examples might help.

HTH,
Elkar

"Valerie" wrote:

This did not work for me. Perhaps if I can understand a few things I could
adjust it to work. Why SUMPRODUCT since I do not need the product of
anything? What does the * do? What does the -- do?
--
Valerie


"Valerie" wrote:

Thank you so much for your help. This looks as though it will do the trick.
--
Valerie


"Elkar" wrote:

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

Yes, some of the cells in that range do have text values. Is there anyway
around this?
--
Valerie


"Elkar" wrote:

The #VALUE! error could also occur if one of the numbers being summed is not
a number. Do you have text values that would be 4 columns to the right of
any of the codes you're looking up?


"Valerie" wrote:

The formula that you so kindly provided seems to be exactly right, however I
get a #value! error. This is supposed to indicate that the array argument
does not have the same dimensions. Does that refer to the range? I did use
the same cell range in both sections of the formula.
--
Valerie


"Elkar" wrote:

As Peo mentioned, the -- is not necessary. That was left over from my
original thought, which I forgot to remove. It really should have no effect
on the formula though.

The use of the SUMPRODUCT function creates an array.

Basically, think of it as each cell in your range is evaluated
independently, then the results are all added together at the end. So, in my
example, we start with cell A1. This is checked to see if it equals the code
123. If yes, it returns TRUE which has a value of 1. If no, it returns
FALSE which has a value of 0. The * symbol then multiplies the 1 or 0 by the
value found 4 cells to the right, in this case E1. Since anything multiplied
by 0 is 0, all false conditions are effectively eliminated from the final
sum. This then continues on for each cell in the range.

Since its still not working, perhaps a better explanation of what you want
along with some examples might help.

HTH,
Elkar

"Valerie" wrote:

This did not work for me. Perhaps if I can understand a few things I could
adjust it to work. Why SUMPRODUCT since I do not need the product of
anything? What does the * do? What does the -- do?
--
Valerie


"Valerie" wrote:

Thank you so much for your help. This looks as though it will do the trick.
--
Valerie


"Elkar" wrote:

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie

a twist on a VLOOKUP?
 

I take that back. Some of the cells in the range have text. However, none
of the cells four columns from the codes have text.
--
Valerie


"Valerie" wrote:

Yes, some of the cells in that range do have text values. Is there anyway
around this?
--
Valerie


"Elkar" wrote:

The #VALUE! error could also occur if one of the numbers being summed is not
a number. Do you have text values that would be 4 columns to the right of
any of the codes you're looking up?


"Valerie" wrote:

The formula that you so kindly provided seems to be exactly right, however I
get a #value! error. This is supposed to indicate that the array argument
does not have the same dimensions. Does that refer to the range? I did use
the same cell range in both sections of the formula.
--
Valerie


"Elkar" wrote:

As Peo mentioned, the -- is not necessary. That was left over from my
original thought, which I forgot to remove. It really should have no effect
on the formula though.

The use of the SUMPRODUCT function creates an array.

Basically, think of it as each cell in your range is evaluated
independently, then the results are all added together at the end. So, in my
example, we start with cell A1. This is checked to see if it equals the code
123. If yes, it returns TRUE which has a value of 1. If no, it returns
FALSE which has a value of 0. The * symbol then multiplies the 1 or 0 by the
value found 4 cells to the right, in this case E1. Since anything multiplied
by 0 is 0, all false conditions are effectively eliminated from the final
sum. This then continues on for each cell in the range.

Since its still not working, perhaps a better explanation of what you want
along with some examples might help.

HTH,
Elkar

"Valerie" wrote:

This did not work for me. Perhaps if I can understand a few things I could
adjust it to work. Why SUMPRODUCT since I do not need the product of
anything? What does the * do? What does the -- do?
--
Valerie


"Valerie" wrote:

Thank you so much for your help. This looks as though it will do the trick.
--
Valerie


"Elkar" wrote:

I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar


"Valerie" wrote:

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
--
Valerie