Vlookup in a Sum formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I need help to integrate a Vlookup in a sum formula.

I have tried this: +SUM(VLOOKUP('TPB Actual'!C10;'TPB Actual'!C10:L10;G84;FALSE)):(VLOOKUP('TPB Actual'!C10;'TPB Actual'!C10:L10;h84;FALSE)).

The formula only works if I use ; instead of : in the middle of the formula. But then the problem is that I get the sum of only 2 cells instead of a sum of values from a row that is defined via the Vlookup formula.

In advance thank you

Mads
 
Hi Mads
if I understood you correctly you're trying to sum a range between your
two found lookup criteria. But I'm not sure which range you wantg to
sum in respect to your example formula? Maybe you can provide the
following information:
- In your formula you're searching for the value in C10 which is also
included in your lookup range??
- G84 and H84 are used as column indices. To which value do they
evaluate???

In generel I would solve this kind of problem with a combination of
SUM, OFFSET and MATCH. e.g.
=SUM(OFFSET($A$1,MATCH(start_value,$A$1:$A$1000,0)-1,0,MATCH(end_value,
$A$1:$A$1000,0)-MATCH(start_value,$A$1:$A$1000,0)+1))

This will sum the defined range between start_value and end_value in
column A

HTH
Frank
 
Mads,

I assume the VLOOKUP returns a cell address, so try

=SUM(INDIRECT(VLOOKUP('TPB Actual'!C10;'TPB
Actual'!C10:L10;G84;FALSE)&":"&VLOOKUP('TPB Actual'!C10;'TPB
Actual'!C10:L10;h84;FALSE)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Mads said:
Hi

I need help to integrate a Vlookup in a sum formula.

I have tried this: +SUM(VLOOKUP('TPB Actual'!C10;'TPB
Click to expand...
Actual'!C10:L10;G84;FALSE)):(VLOOKUP('TPB Actual'!C10;'TPB
Actual'!C10:L10;h84;FALSE)).
The formula only works if I use ; instead of : in the middle of the
Click to expand...
formula. But then the problem is that I get the sum of only 2 cells instead
of a sum of values from a row that is defined via the Vlookup formula.
 
Mads said:
Hi

I need help to integrate a Vlookup in a sum formula.

I have tried this: +SUM(VLOOKUP('TPB Actual'!C10;'TPB
Click to expand...
Actual'!C10:L10;G84;FALSE)):(VLOOKUP('TPB Actual'!C10;'TPB
Actual'!C10:L10;h84;FALSE)).
The formula only works if I use ; instead of : in the middle of the
Click to expand...
formula. But then the problem is that I get the sum of only 2 cells instead
of a sum of values from a row that is defined via the Vlookup formula.
In advance thank you

Mads
Click to expand...

VLOOKUP returns the value in a cell, not the address of that cell. Hence
your formula works with ";" as it then returns two numbers to the SUM (e.g.
=SUM(2;3) ), but not with ":" as the SUM function would not then make sense
(e.g. =SUM(2:3) ).

You will need to use functions such as INDEX and MATCH instead of VLOOKUP.
 
Hi Frank
It's correct that I'm trying to sum a range between two found lookup criterias.

C10 is a normally text and G84 and H84 are column idices in the lookup formulas. They are to define in which column the sum formula must start and where it must end. In G84 and H84 I can type any number and therby define the size of the range that the sum formula must use.

I'm working as a controller and I want to see various YTD results (e.g september, october and so on). The problem is that the figures are monthly figures and I thus need a formula that enables me to sum the monthly figures.

In advance thank you


----- Frank Kabel skrev: -----

Hi Mads
if I understood you correctly you're trying to sum a range between your
two found lookup criteria. But I'm not sure which range you wantg to
sum in respect to your example formula? Maybe you can provide the
following information:
- In your formula you're searching for the value in C10 which is also
included in your lookup range??
- G84 and H84 are used as column indices. To which value do they
evaluate???

In generel I would solve this kind of problem with a combination of
SUM, OFFSET and MATCH. e.g.
=SUM(OFFSET($A$1,MATCH(start_value,$A$1:$A$1000,0)-1,0,MATCH(end_value,
$A$1:$A$1000,0)-MATCH(start_value,$A$1:$A$1000,0)+1))

This will sum the defined range between start_value and end_value in
column A

HTH
Frank
 
Hi Bo

Thanks for your quick answer

I have tried your suggestion, but it doesn't seem to work. The formula just return; "REF

To be more precise i'm looking for a formula that can give me the sum of a range (crow) which size varies when I define it. E.g
Jan Feb March April May ......
Result 2 5 1 4

I would like to be able to make a sum of the result from Jan to April but also from Feb to april. And thus I need a formula in the sum formula that enables me to do so

B

Mad
----- Bob Phillips skrev: ----

Mads

I assume the VLOOKUP returns a cell address, so tr

=SUM(INDIRECT(VLOOKUP('TPB Actual'!C10;'TP
Actual'!C10:L10;G84;FALSE)&":"&VLOOKUP('TPB Actual'!C10;'TP
Actual'!C10:L10;h84;FALSE))

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

Mads said:
H
Click to expand...
Actual'!C10:L10;G84;FALSE)):(VLOOKUP('TPB Actual'!C10;'TP
Actual'!C10:L10;h84;FALSE))formula. But then the problem is that I get the sum of only 2 cells instea
of a sum of values from a row that is defined via the Vlookup formula
 
Hi Mads
If I read you original formula correctly you want to sum 1-n columns in
row 10 (starting in column C). If this is correct try the following
=SUM(OFFSET('TPB Actual'!C10,0,G84-1,1,H84-G84+1))
this will sum all values in row 10 starting at the G84th column of this
row, extending to th H84th column of this row)

HTH
Frank
 
Dear Fran

It seems as if your suggestion is just perfect.

I'm very happy that you were capable of helping me. Thank you very much

Have a nice day

B

Mad

----- Frank Kabel skrev: ----

Hi Mad
If I read you original formula correctly you want to sum 1-n columns i
row 10 (starting in column C). If this is correct try the followin
=SUM(OFFSET('TPB Actual'!C10,0,G84-1,1,H84-G84+1)
this will sum all values in row 10 starting at the G84th column of thi
row, extending to th H84th column of this row

HT
Fran



Mads wrote
 
Hi Mads
thanks for the thanks
Frank
Dear Frank

It seems as if your suggestion is just perfect.

I'm very happy that you were capable of helping me. Thank you very
much.

Have a nice day.

BR

Mads

----- Frank Kabel skrev: -----

Hi Mads
If I read you original formula correctly you want to sum 1-n
columns in row 10 (starting in column C). If this is correct try
the following =SUM(OFFSET('TPB Actual'!C10,0,G84-1,1,H84-G84+1))
this will sum all values in row 10 starting at the G84th column
of this row, extending to th H84th column of this row)

HTH
Frank



the > lookup formulas. They are to define in which column the
sum formula > must start and where it must end. In G84 and H84 I
can type any > number and therby define the size of the range
that the sum formula > must use.
results > (e.g september, october and so on). The problem is
that the figures > are monthly figures and I thus need a formula
that enables me to sum > the monthly figures.
which > range you wantg to sum in respect to your example
formula? Maybe > you can provide the following information:
which is > also included in your lookup range??
=SUM(OFFSET($A$1,MATCH(start_value,$A$1:$A$1000,0)-1,0,MATCH(end_value,
will sum the defined range between start_value and > end_value
in column A >> HTH > Frank >>> Mads wrote: >> Hi >>> I
need help to integrate a Vlookup in a sum formula. >>> I have tried
this: +SUM(VLOOKUP('TPB Actual'!C10;'TPB >>
Actual'!C10:L10;G84;FALSE)):(VLOOKUP('TPB Actual'!C10;'TPB >>
Actual'!C10:L10;h84;FALSE)). >>> The formula only works if I use ;
instead of : in the middle > of the > formula. But then the
problem is that I get the sum of > only 2 cells > instead of a
sum of values from a row that is > defined via the Vlookup >
formula. >>> In advance thank you >>> Mads
Click to expand...
 
Back
Top