formula calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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 ?
 
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)
 
Back
Top