formula calculation
 

I would like to know if it is possible to have a formula to calculate the sum
of all entries if they are posted in different rows so that you do not have
to do a data sort.
for example i have simple worksheet like
column a date column b item column c total cost column d tax colimn e nett
cost
I enter as the Invoice comes in and I want to total all the invoices for the
month e.g jul and put total in column f
and then aug in coulmn g etc

Is this possible without doing a data sort
Thank you for your help
--
bary28

formula calculation
 

Hi Barry

In say column G, enter in G1 01 Jan 2007, G2 01 Feb 2007 down to G12 01
Dec 2007. Format these cells FormatCellsNumberCustom mmm
In H1 enter
=SUMPRODUCT(--(TEXT($A$2;$A$1000,"yymm"=TEXT($G2,"yymm")),C$2;C$ 1000)
Copy down though H2:H12
This will give totals by month for Total cost.
If you want the Totals for Tax and Nett, then copy H1:H12 across through
I1:J1


--
Regards

Roger Govier


"barry28" wrote in message
...
I would like to know if it is possible to have a formula to calculate
the sum
of all entries if they are posted in different rows so that you do not
have
to do a data sort.
for example i have simple worksheet like
column a date column b item column c total cost column d tax colimn e
nett
cost
I enter as the Invoice comes in and I want to total all the invoices
for the
month e.g jul and put total in column f
and then aug in coulmn g etc

Is this possible without doing a data sort
Thank you for your help
--
bary28

formula calculation
 

Thank for your suggestion.
I am however receiving "The formula you typed contains an error"
I have changed the ; to a : but still does not work.
Column a is formatted to dd/mm/yy as this is what I need date month year
from the invoice.
What anm I copying wrong ?
--
bary28


"Roger Govier" wrote:

Hi Barry

In say column G, enter in G1 01 Jan 2007, G2 01 Feb 2007 down to G12 01
Dec 2007. Format these cells FormatCellsNumberCustom mmm
In H1 enter
=SUMPRODUCT(--(TEXT($A$2;$A$1000,"yymm"=TEXT($G2,"yymm")),C$2;C$ 1000)
Copy down though H2:H12
This will give totals by month for Total cost.
If you want the Totals for Tax and Nett, then copy H1:H12 across through
I1:J1


--
Regards

Roger Govier


"barry28" wrote in message
...
I would like to know if it is possible to have a formula to calculate
the sum
of all entries if they are posted in different rows so that you do not
have
to do a data sort.
for example i have simple worksheet like
column a date column b item column c total cost column d tax colimn e
nett
cost
I enter as the Invoice comes in and I want to total all the invoices
for the
month e.g jul and put total in column f
and then aug in coulmn g etc

Is this possible without doing a data sort
Thank you for your help
--
bary28

formula calculation
 

Hi Barry

Many apologies, a case of fingers not typing what brain was thinking -
again<bg
Try
=SUMPRODUCT(--(TEXT($A$2:$A$1000,"yymm")=TEXT($G1,"yymm")),C$2:C $1000)

--
Regards

Roger Govier


"barry28" wrote in message
...
Thank for your suggestion.
I am however receiving "The formula you typed contains an error"
I have changed the ; to a : but still does not work.
Column a is formatted to dd/mm/yy as this is what I need date month
year
from the invoice.
What anm I copying wrong ?
--
bary28


"Roger Govier" wrote:

Hi Barry

In say column G, enter in G1 01 Jan 2007, G2 01 Feb 2007 down to G12
01
Dec 2007. Format these cells FormatCellsNumberCustom mmm
In H1 enter
=SUMPRODUCT(--(TEXT($A$2;$A$1000,"yymm"=TEXT($G2,"yymm")),C$2;C$ 1000)
Copy down though H2:H12
This will give totals by month for Total cost.
If you want the Totals for Tax and Nett, then copy H1:H12 across
through
I1:J1


--
Regards

Roger Govier


"barry28" wrote in message
...
I would like to know if it is possible to have a formula to
calculate
the sum
of all entries if they are posted in different rows so that you do
not
have
to do a data sort.
for example i have simple worksheet like
column a date column b item column c total cost column d tax colimn
e
nett
cost
I enter as the Invoice comes in and I want to total all the
invoices
for the
month e.g jul and put total in column f
and then aug in coulmn g etc

Is this possible without doing a data sort
Thank you for your help
--
bary28

formula calculation
 

Thank you
you are a genius
I appreciate this very much
It has worked.
--
bary28


"Roger Govier" wrote:

Hi Barry

Many apologies, a case of fingers not typing what brain was thinking -
again<bg
Try
=SUMPRODUCT(--(TEXT($A$2:$A$1000,"yymm")=TEXT($G1,"yymm")),C$2:C $1000)

--
Regards

Roger Govier


"barry28" wrote in message
...
Thank for your suggestion.
I am however receiving "The formula you typed contains an error"
I have changed the ; to a : but still does not work.
Column a is formatted to dd/mm/yy as this is what I need date month
year
from the invoice.
What anm I copying wrong ?
--
bary28


"Roger Govier" wrote:

Hi Barry

In say column G, enter in G1 01 Jan 2007, G2 01 Feb 2007 down to G12
01
Dec 2007. Format these cells FormatCellsNumberCustom mmm
In H1 enter
=SUMPRODUCT(--(TEXT($A$2;$A$1000,"yymm"=TEXT($G2,"yymm")),C$2;C$ 1000)
Copy down though H2:H12
This will give totals by month for Total cost.
If you want the Totals for Tax and Nett, then copy H1:H12 across
through
I1:J1


--
Regards

Roger Govier


"barry28" wrote in message
...
I would like to know if it is possible to have a formula to
calculate
the sum
of all entries if they are posted in different rows so that you do
not
have
to do a data sort.
for example i have simple worksheet like
column a date column b item column c total cost column d tax colimn
e
nett
cost
I enter as the Invoice comes in and I want to total all the
invoices
for the
month e.g jul and put total in column f
and then aug in coulmn g etc

Is this possible without doing a data sort
Thank you for your help
--
bary28