Is it possible to do that using excel formulas ?

[Grek]

Hi All,

I'm working an a small macro to make my daily work easier
Unfortunately I'm not good enough in excel to make it 100% by myself
That's why I would really much appreciate your help on this ;-)

I've attached the file in this post.

Here is the problem :

Below are the datas from the sheet "Estim - FX contracts by Fund"

SELL JPY 496.000,00 BUY USD 16/07/2004
SELL USD 15.234,59 BUY EUR 19/07/2004
BUY EUR -8.711,74 SELL USD 19/07/2004
SELL USD 630.909,49 BUY EUR 19/07/2004
SELL CHF 136,78 BUY EUR 19/07/2004
BUY USD -11.563,43 SELL JPY 16/07/2004
BUY USD -10.168,00 SELL JPY 16/07/2004
BUY USD -398,02 SELL JPY 20/07/2004


Here are the results from the sheet I would like to be generate
automaticly in the sheet "Estim -Aggregate FX contracts". It's in fac
a summary of the above datas by currency and same value date.


SELL JPY 496.000,0 BUY USD 16/07/2004
SELL USD 646.144,08 BUY EUR 19/07/2004
SELL CHF 136,78 BUY EUR 19/07/2004
BUY USD -21.731,43 SELL JPY 16/07/2004
BUY USD -398,02 SELL JPY 20/07/2004
BUY EUR -8.711,74 SELL USD 19/07/2004


For instance 646.144,08 is the sum of those 2 lines :
SELL USD 15.234,59 BUY EUR 19/07/2004
SELL USD 630.909,49 BUY EUR 19/07/2004

Does anyone has an idea how I could do that ? I don't think tha
sumprod would work in that case. Would it ?

Thank you very much in advance,

Gre

Attachment filename: gregsmacro.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=62035

 

[Jerry W. Lewis]

If the information from a row is separated into different columns for
easy identification, then SUMPRODUCT would probably work just fine. For
Example

=SUMPRODUCT((A1:A10="SELL USD")*(C1:C10="BUY
EUR")*(D110=DATEVALUE("19/07/2004")),B1:B10)

Jerry

 

[Don Guillett]

for all dates try
=SUMPRODUCT((E3:E10="SELL")*(F3:F10="USD")*G3:G10)
& for the date change your date to fit
=SUMPRODUCT((E3:E10="SELL")*(F3:F10="USD")*(K3:K10=DATEVALUE("7/19/2004"))*G
3:G10)
I'm not quite sure of your criteria but the k valuation could be based on a
cell k3:k10=k3