Formula for summing purchases and sales

[Helixal]

a b c d `f g h i

1 date nane1 buy 34 99

2 date name1 sell 24 56

3 date name2 buy 56 99

4 date name3 buy 36 103

5 date name 3 buy 28 34

6 date name3 sell 12 470

I want to create an excell spread sheet of 8 columns and unlimited rows.
I'd like column h to be the sum of the numbers in column f for each name.
where buy would be a positive number and sell would be a negative number.
In this example the number 10 (34-24) would go in cloumn f row 2. These are
the sum of buys and sells for name1. Name2 would be 56 in row 3 and name3
would be 76 in row 6. Any row could contain words other that "buy" or "sell"
in which case that entire row should be ignored. If that happens to be the
last row of any given name then the result should be placed on that row.

Similarly i'd like to sum column g and place the result in column i. In
this case however the word buy has negative meaning and the word sell has a
positive meaning. So row 2 i would contain -43. 3i would contain -99 and
6i would contain 333.

Think of these as purchases and sales of securities. Name1 could stand for
the purchase of 34 shares of AT&T for $99 and the sale of 24 shares at $56.
The result would be 10 shares and $-43 (LOSS)

The trick is to sum the numbers till the name changes. There are no row
delimiters between names.

Help would be appreciated.

 

[Michael Bednarek]

[snip]

I want to create an excell spread sheet of 8 columns and unlimited rows.
I'd like column h to be the sum of the numbers in column f for each name.
where buy would be a positive number and sell would be a negative number.
In this example the number 10 (34-24) would go in cloumn f row 2. These are
the sum of buys and sells for name1. Name2 would be 56 in row 3 and name3
would be 76 in row 6. Any row could contain words other that "buy" or "sell"
in which case that entire row should be ignored. If that happens to be the
last row of any given name then the result should be placed on that row.

Similarly i'd like to sum column g and place the result in column i. In
this case however the word buy has negative meaning and the word sell has a
positive meaning. So row 2 i would contain -43. 3i would contain -99 and
6i would contain 333.

Think of these as purchases and sales of securities. Name1 could stand for
the purchase of 34 shares of AT&T for $99 and the sale of 24 shares at $56.
The result would be 10 shares and $-43 (LOSS)

The trick is to sum the numbers till the name changes. There are no row
delimiters between names.

A combination of SUMPRODUCT array formulas and Excel's Data/Subtotals
command might be able to do what you want.

 

[Helixal]

Could you give me an example of how this would be done for one of the target
columns?

[snip]

I want to create an excell spread sheet of 8 columns and unlimited rows.
I'd like column h to be the sum of the numbers in column f for each name.
where buy would be a positive number and sell would be a negative number.
In this example the number 10 (34-24) would go in cloumn f row 2. These
are
the sum of buys and sells for name1. Name2 would be 56 in row 3 and
name3
would be 76 in row 6. Any row could contain words other that "buy" or
"sell"
in which case that entire row should be ignored. If that happens to be
the
last row of any given name then the result should be placed on that row.

Similarly i'd like to sum column g and place the result in column i. In
this case however the word buy has negative meaning and the word sell has
a
positive meaning. So row 2 i would contain -43. 3i would contain -99 and
6i would contain 333.

Think of these as purchases and sales of securities. Name1 could stand
for
the purchase of 34 shares of AT&T for $99 and the sale of 24 shares at
$56.
The result would be 10 shares and $-43 (LOSS)

The trick is to sum the numbers till the name changes. There are no row
delimiters between names.

A combination of SUMPRODUCT array formulas and Excel's Data/Subtotals
command might be able to do what you want.

 

[Michael Bednarek]

Could you give me an example of how this would be done for one of the target
columns?

[snip]

The example you gave is not quite clear to me, nor is what exactly you
require. The names of the columns you give (A:I) do not correspond to
your description of "8 columns". It is also not clear what "result" you
want in the subtotal rows.

First, I added a header row to your table, useful for descriptive
purposes, necessary for some analytical tools. Second, I entered
buy/sale units and amounts as positive/negative numbers (the first buy
as +34 units for an amount of -99, the second is -24 units and an amount
of +56); it may be possible to use the text value of "buy"/"sell" in the
formulae to effect the required sign, but that would unnecessarily
complicate the case for a brief answer.

Then, try this: cell G3: =E3+SUMIF($C$2:C2,C3,$E$2:E2) and
H3: =F3+SUMIF($C$2:C2,C3,$F$2:F2); drag those down for the remaining
rows. G2 and H2 are simply =E2 and =F2, respectively.

The Excel menu command "Subtotals" can be used to insert total lines at
the change of the value in certain columns (needs headers). However, I
can't think of any kind of total suitable for such a line.

You mention "unlimited rows", but Excel spreadsheets are limited in how
many rows they can carry. For much more flexibility in the size of the
dataset and for your reporting requirements, Excel may well be not quite
the right tool and you might want to consider a database program like
Access instead.

 

[Helixal]

Michael,

The two formulas appear to work beautifully.
In the modified spreadsheet attached , the formulas become:

G2=E2+SUMIF($C$1:C1,C2,$E$1:E1)
H2=F2+SUMIF($C$1:C1,C2,$F$1:F1)
in the attached file TEST1.xls.

In the modified spreadsheet I manually inserted the minuses.

Could you suggest a formula for inserting a "-" in column F for a buy and
"-" in coulum G for a sell. The real spreadsheet is currently 650 rows in
length and this would be helpful. In my original posting I mentioned that
the spreadsheet had "unlimited rows". As the spreadsheet grows I can adjust
the maximum number of rows.


Many thanks,
Helixal

Could you give me an example of how this would be done for one of the
target
columns?

[snip]

The example you gave is not quite clear to me, nor is what exactly you
require. The names of the columns you give (A:I) do not correspond to
your description of "8 columns". It is also not clear what "result" you
want in the subtotal rows.

First, I added a header row to your table, useful for descriptive
purposes, necessary for some analytical tools. Second, I entered
buy/sale units and amounts as positive/negative numbers (the first buy
as +34 units for an amount of -99, the second is -24 units and an amount
of +56); it may be possible to use the text value of "buy"/"sell" in the
formulae to effect the required sign, but that would unnecessarily
complicate the case for a brief answer.

Then, try this: cell G3: =E3+SUMIF($C$2:C2,C3,$E$2:E2) and
H3: =F3+SUMIF($C$2:C2,C3,$F$2:F2); drag those down for the remaining
rows. G2 and H2 are simply =E2 and =F2, respectively.

The Excel menu command "Subtotals" can be used to insert total lines at
the change of the value in certain columns (needs headers). However, I
can't think of any kind of total suitable for such a line.

You mention "unlimited rows", but Excel spreadsheets are limited in how
many rows they can carry. For much more flexibility in the size of the
dataset and for your reporting requirements, Excel may well be not quite
the right tool and you might want to consider a database program like
Access instead.

 

[Helixal]

I have been so far unsuccessful in posting the spreadsheet TEST1.xls. It's
only 16 KB in size. I will try to start a new thread and see if that works.
Any suggestions would be appreciated.

Michael,

The two formulas appear to work beautifully.
In the modified spreadsheet attached , the formulas become:

G2=E2+SUMIF($C$1:C1,C2,$E$1:E1)
H2=F2+SUMIF($C$1:C1,C2,$F$1:F1)
in the attached file TEST1.xls.

In the modified spreadsheet I manually inserted the minuses.

Could you suggest a formula for inserting a "-" in column F for a buy and
"-" in coulum G for a sell. The real spreadsheet is currently 650 rows in
length and this would be helpful. In my original posting I mentioned that
the spreadsheet had "unlimited rows". As the spreadsheet grows I can
adjust
the maximum number of rows.


Many thanks,
Helixal

Could you give me an example of how this would be done for one of the
target
columns?

[snip]

The example you gave is not quite clear to me, nor is what exactly you
require. The names of the columns you give (A:I) do not correspond to
your description of "8 columns". It is also not clear what "result" you
want in the subtotal rows.

First, I added a header row to your table, useful for descriptive
purposes, necessary for some analytical tools. Second, I entered
buy/sale units and amounts as positive/negative numbers (the first buy
as +34 units for an amount of -99, the second is -24 units and an amount
of +56); it may be possible to use the text value of "buy"/"sell" in the
formulae to effect the required sign, but that would unnecessarily
complicate the case for a brief answer.

Then, try this: cell G3: =E3+SUMIF($C$2:C2,C3,$E$2:E2) and
H3: =F3+SUMIF($C$2:C2,C3,$F$2:F2); drag those down for the remaining
rows. G2 and H2 are simply =E2 and =F2, respectively.

The Excel menu command "Subtotals" can be used to insert total lines at
the change of the value in certain columns (needs headers). However, I
can't think of any kind of total suitable for such a line.

You mention "unlimited rows", but Excel spreadsheets are limited in how
many rows they can carry. For much more flexibility in the size of the
dataset and for your reporting requirements, Excel may well be not quite
the right tool and you might want to consider a database program like
Access instead.

 

[Helixal]

Here I have inserted the spreadsheet contents as text.
date1 name1 buy 34 -99 34 -99
date2 name1 sell -24 56 10 -43
date3 name2 buy 56 -99 56 -99
date4 name3 buy 36 -103 36 -103
date5 name3 buy 28 -34 64 -137
date6 name3 sell -12 470 52 333
0 0
0 0
0 0
0 0
0 0
0 0
0 0

I have been so far unsuccessful in posting the spreadsheet TEST1.xls. It's
only 16 KB in size. I will try to start a new thread and see if that
works. Any suggestions would be appreciated.

Michael,

The two formulas appear to work beautifully.
In the modified spreadsheet attached , the formulas become:

G2=E2+SUMIF($C$1:C1,C2,$E$1:E1)
H2=F2+SUMIF($C$1:C1,C2,$F$1:F1)
in the attached file TEST1.xls.

In the modified spreadsheet I manually inserted the minuses.

Could you suggest a formula for inserting a "-" in column F for a buy and
"-" in coulum G for a sell. The real spreadsheet is currently 650 rows
in
length and this would be helpful. In my original posting I mentioned
that
the spreadsheet had "unlimited rows". As the spreadsheet grows I can
adjust
the maximum number of rows.


Many thanks,
Helixal

Could you give me an example of how this would be done for one of the
target
columns?
[snip]

The example you gave is not quite clear to me, nor is what exactly you
require. The names of the columns you give (A:I) do not correspond to
your description of "8 columns". It is also not clear what "result" you
want in the subtotal rows.

First, I added a header row to your table, useful for descriptive
purposes, necessary for some analytical tools. Second, I entered
buy/sale units and amounts as positive/negative numbers (the first buy
as +34 units for an amount of -99, the second is -24 units and an amount
of +56); it may be possible to use the text value of "buy"/"sell" in the
formulae to effect the required sign, but that would unnecessarily
complicate the case for a brief answer.

Then, try this: cell G3: =E3+SUMIF($C$2:C2,C3,$E$2:E2) and
H3: =F3+SUMIF($C$2:C2,C3,$F$2:F2); drag those down for the remaining
rows. G2 and H2 are simply =E2 and =F2, respectively.

The Excel menu command "Subtotals" can be used to insert total lines at
the change of the value in certain columns (needs headers). However, I
can't think of any kind of total suitable for such a line.

You mention "unlimited rows", but Excel spreadsheets are limited in how
many rows they can carry. For much more flexibility in the size of the
dataset and for your reporting requirements, Excel may well be not quite
the right tool and you might want to consider a database program like
Access instead.

 

[Michael Bednarek]

[snip]

Could you suggest a formula for inserting a "-" in column F for a buy and
"-" in coulum G for a sell. The real spreadsheet is currently 650 rows in
length and this would be helpful.

[snip]

The usual way to do this is to create a "helper column" with the
appropriate formula, then Copy/Paste Special (Values) the results back
into the original column and delete the "helper column". The formula
might be: =IF(D2="sell",-E2,E2); drag down.

 

[Helixal]

I'm wondering if a procedure (Macro?) could do this. To complicate matters,
in addition to buy and sell there are also add (requiring the same treatment
as buy) and remove(same as sell) and possibly something(s) else requiring no
treatemnt. After running this procedure to add the +'s and -'s, the macro
could then perform the summation to create the additional columns G and H
discussed earlier..

[snip]

Could you suggest a formula for inserting a "-" in column F for a buy and
"-" in coulum G for a sell. The real spreadsheet is currently 650 rows in
length and this would be helpful.

[snip]

The usual way to do this is to create a "helper column" with the
appropriate formula, then Copy/Paste Special (Values) the results back
into the original column and delete the "helper column". The formula
might be: =IF(D2="sell",-E2,E2); drag down.