Vlookup? #2

Can I use VLOOKUP to look up data in a data base and total multiple
values associated with the lookup number in a range?
Example:

Database
#          $
700      $325.
650      $225.
310      $110.
700      $500.
115      $475.
700      $125


Result #700= $950


---
Message posted from http://www.ExcelForum.com/

0
12/31/2003 3:07:03 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
275 Views

Similar Articles

[PageSpeed] 14

Vlookup is not the tool to use here.

Try using SUMIF, it will fit the task nicely.  (Also look at COUNTIF,
DSUM for similar functions)


---
Message posted from http://www.ExcelForum.com/

0
12/31/2003 3:10:43 AM
As Kieran says, try SUMIF instead of VLOOKUP

Here's some steps to ease you-in on using SUMIF
for the sample table you provided in your post

Assume your sample table below is say,
in Sheet1, A1:B7
--------------------
> #          $
> 700      $325.
> 650      $225.
> 310      $110.
> 700      $500.
> 115      $475.
> 700      $125

In another sheet, say Sheet2,
you have listed in col A:
---------------------------
#
700
650
115
etc

Put in B2: =SUMIF(Sheet1!$A$2:$A$7,A2,Sheet1!$B$2:$B$7)
Copy B2 down col B

Col B will give you the totals for each number listed in col A

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
"BRustigian" <BRustigian.za76m@excelforum-nospam.com> wrote in message
news:BRustigian.za76m@excelforum-nospam.com...
> Can I use VLOOKUP to look up data in a data base and total multiple
> values associated with the lookup number in a range?
> Example:
>
> Database
> #          $
> 700      $325.
> 650      $225.
> 310      $110.
> 700      $500.
> 115      $475.
> 700      $125
>
>
> Result #700= $950
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
demechanik (4694)
12/31/2003 3:56:42 AM
Add headers to each column, then

either:  
Sort your data and then Data|Subtotal

Or:
leave your data unsorted
Select your range
Data|Pivottable
follow that wizard until you get to a step with a Layout button on it.
click that Layout button
Drag the (column A) field to the row field
drag the price/cost/whatever it is to the Data field
(if that says "count of", double click on it and choose Sum.)

then finish the wizard

If you change the raw data, remember to refresh your pivottable--it's not
automatic.


BRustigian wrote:
> 
> Can I use VLOOKUP to look up data in a data base and total multiple
> values associated with the lookup number in a range?
> Example:
> 
> Database
> #          $
> 700      $325.
> 650      $225.
> 310      $110.
> 700      $500.
> 115      $475.
> 700      $125
> 
> Result #700= $950
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/31/2003 3:59:24 AM
If the functions in the freely downloadable file at 
http://home.pacbell.net/beban are available to your workbook:

=SUM(VLookups(700,A1:B6,2))

Alan Beban

BRustigian wrote:
> Can I use VLOOKUP to look up data in a data base and total multiple
> values associated with the lookup number in a range?
> Example:
> 
> Database
> #          $
> 700      $325.
> 650      $225.
> 310      $110.
> 700      $500.
> 115      $475.
> 700      $125
> 
> 
> Result #700= $950
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
12/31/2003 7:38:07 AM
Sorry to gatecrash into this thread...but was wondering is it possible 
to give more than one condition in SUMIF?
Maybe

700  January 325
600  January  425
700  February 555
700  January  400

I would need to sum values in column three if column 1 is 700 AND column 
2 is January

Can that be done using SumIF?

rEgards,
R

Max wrote:
> As Kieran says, try SUMIF instead of VLOOKUP
> 
> Here's some steps to ease you-in on using SUMIF
> for the sample table you provided in your post
> 
> Assume your sample table below is say,
> in Sheet1, A1:B7
> --------------------
> 
>>#          $
>>700      $325.
>>650      $225.
>>310      $110.
>>700      $500.
>>115      $475.
>>700      $125
> 
> 
> In another sheet, say Sheet2,
> you have listed in col A:
> ---------------------------
> #
> 700
> 650
> 115
> etc
> 
> Put in B2: =SUMIF(Sheet1!$A$2:$A$7,A2,Sheet1!$B$2:$B$7)
> Copy B2 down col B
> 
> Col B will give you the totals for each number listed in col A
> 
> --
> hth
> Max
> -----------------------------------------
> Please reply in thread
> Use xdemechanik <at>yahoo<dot>com for email
> --------------------------------------------------------
> "BRustigian" <BRustigian.za76m@excelforum-nospam.com> wrote in message
> news:BRustigian.za76m@excelforum-nospam.com...
> 
>>Can I use VLOOKUP to look up data in a data base and total multiple
>>values associated with the lookup number in a range?
>>Example:
>>
>>Database
>>#          $
>>700      $325.
>>650      $225.
>>310      $110.
>>700      $500.
>>115      $475.
>>700      $125
>>
>>
>>Result #700= $950
>>
>>
>>---
>>Message posted from http://www.ExcelForum.com/
>>
> 
> 
> 

0
1/5/2004 7:50:26 AM
Try SUMPRODUCT

Assuming your sample data is in Sheet3, in A2:C5

In say, Sheet4
you have listed in A2:B3

700 January
700 February

Put in C2:
=SUMPRODUCT((Sheet3!$A$2:$A$5=A2)*(Sheet3!$B$2:$B$5=B2)*Sheet3!$C$2:$C$5)

C2 returns the sum of values in Sheet3's col C range
where Sheet3's col A range = 700 and Sheet3's col B range = "January"

Copy C2 down to C3

Note: The 3 ranges: $A$2:$A$5, $B$2:$B$5, $C$2:$C$5 must be identical
structure
and you cannot use entire column references, e.g.: A:A, B:B, C:C in
sumproduct

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
"Rajiv Chandran" <Rajiv.Chandran@Oracle.com> wrote in message
news:AP8Kb.1$So5.68@news.oracle.com...
> Sorry to gatecrash into this thread...but was wondering is it possible
> to give more than one condition in SUMIF?
> Maybe
>
> 700  January 325
> 600  January  425
> 700  February 555
> 700  January  400
>
> I would need to sum values in column three if column 1 is 700 AND column
> 2 is January
>
> Can that be done using SumIF?
>
> rEgards,
> R
>
> Max wrote:
> > As Kieran says, try SUMIF instead of VLOOKUP
> >
> > Here's some steps to ease you-in on using SUMIF
> > for the sample table you provided in your post
> >
> > Assume your sample table below is say,
> > in Sheet1, A1:B7
> > --------------------
> >
> >>#          $
> >>700      $325.
> >>650      $225.
> >>310      $110.
> >>700      $500.
> >>115      $475.
> >>700      $125
> >
> >
> > In another sheet, say Sheet2,
> > you have listed in col A:
> > ---------------------------
> > #
> > 700
> > 650
> > 115
> > etc
> >
> > Put in B2: =SUMIF(Sheet1!$A$2:$A$7,A2,Sheet1!$B$2:$B$7)
> > Copy B2 down col B
> >
> > Col B will give you the totals for each number listed in col A
> >
> > --
> > hth
> > Max
> > -----------------------------------------
> > Please reply in thread
> > Use xdemechanik <at>yahoo<dot>com for email
> > --------------------------------------------------------
> > "BRustigian" <BRustigian.za76m@excelforum-nospam.com> wrote in message
> > news:BRustigian.za76m@excelforum-nospam.com...
> >
> >>Can I use VLOOKUP to look up data in a data base and total multiple
> >>values associated with the lookup number in a range?
> >>Example:
> >>
> >>Database
> >>#          $
> >>700      $325.
> >>650      $225.
> >>310      $110.
> >>700      $500.
> >>115      $475.
> >>700      $125
> >>
> >>
> >>Result #700= $950
> >>
> >>
> >>---
> >>Message posted from http://www.ExcelForum.com/
> >>
> >
> >
> >
>


0
demechanik (4694)
1/5/2004 8:54:39 AM
Reply:

Similar Artilces: