MACRO QUESTION

[Lindsay Graham]

I have a spreadsheet that has 5 columns -- Date, Buyer Code, Value of
Purchase, Seller Code, Value of Sale. Each row is a buy/sell transaction
between a buyer and a seller, so (Value of Sale) = -(Value of Purchase).
The buyers and sellers are a closed group, so that the group of Buyer codes
is the same as the group of Seller codes.

There is a macro which creates a worksheet called Buyers (deleting any
existing worksheet of that name) containing subtotals of the Value of
Purchase column for each Buyer Code. Similarly, there is another macro
which creates a worksheet called Sellers containing subtotals of the Value
of Sale column for each Seller Code.

My question is -- how can I get the Buyers Value of Purchase subtotals and
the Sellers Value of Sales subtotals on the same worksheet?

Hope this makes sense, and that someone can help.

Lindsay Graham
Canberra, Australia

 

[Alex Delamain]

If I understand you correctly then you need that most wonderful o
functions: SUMPRODUCT

for each buyer code


=sumproduct((b1:b100=buyercode)*(c1:c100))

Then do the same for the sellers

=sumproduct((d1:d100=sellercode)*(e1:e100))

Then if you really want you can add extra terms for example to selec
totals between certain dates

=sumproduct((a1:a100>=startdate)*(a1:a100<=enddate)*(b1:b100=buyercode)*(c1:c100)