Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation / Formula HELP | Excel Worksheet Functions | |||
Changes to Calculation Formula | New Users to Excel | |||
Calculation vs formula | Excel Worksheet Functions | |||
calculation in an if formula | Excel Worksheet Functions | |||
i need help with a calculation formula... | Excel Worksheet Functions |