formula calculation

[Guest]

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

 

[Roger Govier]

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 Format>Cells>Number>Custom> 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

 

[Guest]

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 ?

 

[Roger Govier]

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)

 

[Guest]

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