Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 | |
|
|
![]() |
||||
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 |