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

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

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

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

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

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

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

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

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

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