Vlookup?

  • Thread starter Thread starter BRustigian
  • Start date Start date
B

BRustigian

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
 
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)
 
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
Click to expand...

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
 
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.
 
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
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
Click to expand...


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
Click to expand...
 
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 said:
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
Click to expand...
 
Back
Top