I'm using an excel macros to subtotal my sales and purchases. All my sales
transcation are listed on top of my purchases and there are several blank
lines to separate the two. How do I subtract the grand total of my sales
from the grand total of purchases if both grand totals are on the same
column? Since the amount of sales and purchases transactions varies, i don't
know the exact cell that either one of the grand total will be in. I do know
that they will be in column "D".

Is there a formula that can find the grand total of the purchases and sales
and subtract them?

Have you thought about using positive numbers for one (sales or purchases) and
negative numbers for the other (purchases or sales)?

Then you could just sum the column to get the net amount.

Or put an indicator in another column (Say column E) and use that:

=sumif(e:e,"Sales",d:d) - sumif(e:e,"Purchase",d:d)

or
=sumif(e:e,"Sales Total",d:d) - sumif(e:e,"Purchases Total",d:d)

Or whatever you have to indicate the grand totals.

David T wrote:

I'm using an excel macros to subtotal my sales and purchases. All my sales
transcation are listed on top of my purchases and there are several blank
lines to separate the two. How do I subtract the grand total of my sales
from the grand total of purchases if both grand totals are on the same
column? Since the amount of sales and purchases transactions varies, i don't
know the exact cell that either one of the grand total will be in. I do know
that they will be in column "D".

Is there a formula that can find the grand total of the purchases and sales
and subtract them?

--

Dave Peterson

aHey Dave-

Thanks for your help. All of my data is being pulled from my company's
oracle database so sales and purchases are not distinguished by a positive or
a negative.

Is there any way that when i subtotal the sales , I can label that subtotal
as "Sales"
and the purchases as "Purchases" so that the formula can distinguish which
are the "Purchases" and which are the "Sales"? I thank you in advance.












"Dave Peterson" wrote:

Have you thought about using positive numbers for one (sales or purchases) and
negative numbers for the other (purchases or sales)?

Then you could just sum the column to get the net amount.

Or put an indicator in another column (Say column E) and use that:

=sumif(e:e,"Sales",d:d) - sumif(e:e,"Purchase",d:d)

or
=sumif(e:e,"Sales Total",d:d) - sumif(e:e,"Purchases Total",d:d)

Or whatever you have to indicate the grand totals.

David T wrote:

I'm using an excel macros to subtotal my sales and purchases. All my sales
transcation are listed on top of my purchases and there are several blank
lines to separate the two. How do I subtract the grand total of my sales
from the grand total of purchases if both grand totals are on the same
column? Since the amount of sales and purchases transactions varies, i don't
know the exact cell that either one of the grand total will be in. I do know
that they will be in column "D".

Is there a formula that can find the grand total of the purchases and sales
and subtract them?

--

Dave Peterson

I think I'd use a separate field (a different column) that indicated what the
value represented.

David T wrote:

aHey Dave-

Thanks for your help. All of my data is being pulled from my company's
oracle database so sales and purchases are not distinguished by a positive or
a negative.

Is there any way that when i subtotal the sales , I can label that subtotal
as "Sales"
and the purchases as "Purchases" so that the formula can distinguish which
are the "Purchases" and which are the "Sales"? I thank you in advance.

"Dave Peterson" wrote:

Have you thought about using positive numbers for one (sales or purchases) and
negative numbers for the other (purchases or sales)?

Then you could just sum the column to get the net amount.

Or put an indicator in another column (Say column E) and use that:

=sumif(e:e,"Sales",d:d) - sumif(e:e,"Purchase",d:d)

or
=sumif(e:e,"Sales Total",d:d) - sumif(e:e,"Purchases Total",d:d)

Or whatever you have to indicate the grand totals.

David T wrote:

I'm using an excel macros to subtotal my sales and purchases. All my sales
transcation are listed on top of my purchases and there are several blank
lines to separate the two. How do I subtract the grand total of my sales
from the grand total of purchases if both grand totals are on the same
column? Since the amount of sales and purchases transactions varies, i don't
know the exact cell that either one of the grand total will be in. I do know
that they will be in column "D".

Is there a formula that can find the grand total of the purchases and sales
and subtract them?

--

Dave Peterson


--

Dave Peterson