Solution to "sum if used with a vlookup "??????

  • Thread starter Thread starter 2-4-04
  • Start date Start date
2

2-4-04

Currently I have two worksheets:
1. contains supplier information, check amount, and other
data.
2. this one i want to contain only supplier number, name,
and check amount. So i've used a vlookup in worksheet 2 to
retrieve the check amount as its entered into spreadsheet
1.

Problem is there are multiple supplier numbers in sheet 1.
How do I get the total of all checks entered for one
supplier to show up in sheet 2? The vlookup formula is
only bringing back the first check amount it sees not the
sum of.

any and all suggestions welcome.

thanks in advance...
 
Hi
Assuming your Supplier name is in Column A of Sheet1 and the Check Amounts
are in Column D

On Sheet2 with the Supplier Name in A1, enter in B1
=SUMPRODUCT(--(Sheet1!A1:A100=A1)*(Sheet1!D1:D100))
Change ranges to suit, but endure the length of both ranges is identical.
 
not to sound dumb, but what are the two dashes for...

and when i tried the formula it returned #num...

please advise.

thanks a million!
 
Hi
first to your error. Are there any text entries in the range D1:D100
(e.g. a heading row). If yes change the formula to
=SUMPRODUCT(--(Sheet1!A2:A100=A1)*(Sheet1!D2:D100))
or
=SUMPRODUCT(--(Sheet1!A1:A100=A1),(Sheet1!D1:D100))

The two dashes convert the boolean values TRUE/FALSE to a number (1/0).
'--' is the same as (-1)*(-1). This mathematical operation forces Excel
to convert booleans to numbers. Another way would be
=SUMPRODUCT((Sheet1!A1:A100=A1)+0,(Sheet1!D1:D100))

HTH
Frank

In the column
 
The 2 minus signs are there to coerce the values of TRUE and FALSE to their
numeric equivalents of 1 and 0.
Either the values in each of the cells A1:A100 are equal to the Supplier
name held in cell A1 on Sheet2 (True) or they are not (False) which gets
changed to 1 or 0.
This is then multiplied by the values found in the equivalent row in column
B, and the product is then summed to give your answer.
If you are getting a #NUM error, it sounds as though your data is not all
numeric.

Enter a 1 into another cell on your sheet1.
Copy it.
Mark the range of your check value data
Paste Special=>Multiply
This should change any text values to numeric values.
 
I'll do it this way.

=SUMIF(Sheet1:RANGE,Sheet2!value,SUM_RANGE)
It'll do the vlookup for you. All you would have to do is
type the name of company or index name you've created.
 
Hi Don,

I happen to have recently cerated something similar to what yo
request. It's a two spreadsheet workbook. One sheet contains all th
data, Inamed it "Data", as well as AutoFilter on the Colunm Headers
The second sheet only displays what I want it to, i called i
"Summary". On Summary, I have placed buttons with macros that go t
Data and execute the filters and copy+paste the info displayed. Onc
the info in in Summary (with the same Coumn headers) I applied th
=SUBTOTAL function, in order to have the total only for the displaye
info. Also, there is a part of Sumary that shows all the totals pe
Vendor, but that's hidden and there's a button that displays it i
needed. those totals are brought using =SUMIF. Important thing. O
Sumary, the these totals and buttons I'm talking about are located a
the top of the sheet, so whatever info is brought from Data can b
placed on it, no matter how many rows if info. I have attached a pic o
Summary, i hope u get to see it.

If you think this helps you, feel free to drop a line if you nee
anymore detail or help, i'd be mroe than happy to collaborate, my emai
address should be displayed.

Cheers!


Currently I have two worksheets:
1. contains supplier information, check amount, and other
data.
2. this one i want to contain only supplier number, name,
and check amount. So i've used a vlookup in worksheet 2 to
retrieve the check amount as its entered into spreadsheet
1.

Problem is there are multiple supplier numbers in sheet 1.
How do I get the total of all checks entered for one
supplier to show up in sheet 2? The vlookup formula is
only bringing back the first check amount it sees not the
sum of.

any and all suggestions welcome.

thanks in advance..
Click to expand...

Attachment filename: excel2.gif
Download attachment: http://www.excelforum.com/attachment.php?postid=47106
 
Back
Top