Re: Solution to "sum if used with a vlookup "??????
From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 02/04/04
- Next message: Norman Harker: "Re: In Excel 98 and in Office 2000 How to concatenate two separate cells into one"
- Previous message: Gord Dibben: "Re: Path in Excel footer"
- In reply to: anonymous_at_discussions.microsoft.com: "Re: Solution to "sum if used with a vlookup "??????"
- Next in thread: Roger Govier: "Re: Solution to "sum if used with a vlookup "??????"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 4 Feb 2004 23:41:14 +0100
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
anonymous@discussions.microsoft.com wrote:
> 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!
>
>> -----Original Message-----
>> 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. --
>> Regards
>> Roger Govier
>> "2-4-04" <excel_accor@discussions.microsoft.com> wrote in message
>> news:74fd01c3eb45$ea7542d0$7d02280a@phx.gbl...
>>> 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...
>>>
>>
>>
>> .
- Next message: Norman Harker: "Re: In Excel 98 and in Office 2000 How to concatenate two separate cells into one"
- Previous message: Gord Dibben: "Re: Path in Excel footer"
- In reply to: anonymous_at_discussions.microsoft.com: "Re: Solution to "sum if used with a vlookup "??????"
- Next in thread: Roger Govier: "Re: Solution to "sum if used with a vlookup "??????"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
