Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary Sheet
I have a work sheet where I record daily costs for each item used.
I have a seperate summary sheet which I want to show that days cost for an item and a cumulative total for each item. I have tried VLookUp but cannot seem to get it to roll on each day Any help would be appreciated -- JohnM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary Sheet
John,
You don't say how your sheets are laid out so I am assuming that dates ate in Column A, Items in Column B and Amounts in Columns C. For today's total for a Item 1: =SUMPRODUCT((A1:A125=TODAY())*(B1:B125="Item 1")*(C1:C125)) For the Cumulative total for Item 1: =SUMIF(B:B,"Item 1",C:C) Note that SUMPRODUCT() cannot use whole columns but SUMIF() can. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "JohnM" wrote in message ... I have a work sheet where I record daily costs for each item used. I have a seperate summary sheet which I want to show that days cost for an item and a cumulative total for each item. I have tried VLookUp but cannot seem to get it to roll on each day Any help would be appreciated -- JohnM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary Sheet
Sory should have explained more
Items are in Col A Dates Run across the columns D,E,F,G etc The Items are split into sections, at the bottom of each section there is a total for that day and a cumulative total. These are what should be shown o the summary Thanks -- JohnM "Sandy Mann" wrote: John, You don't say how your sheets are laid out so I am assuming that dates ate in Column A, Items in Column B and Amounts in Columns C. For today's total for a Item 1: =SUMPRODUCT((A1:A125=TODAY())*(B1:B125="Item 1")*(C1:C125)) For the Cumulative total for Item 1: =SUMIF(B:B,"Item 1",C:C) Note that SUMPRODUCT() cannot use whole columns but SUMIF() can. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "JohnM" wrote in message ... I have a work sheet where I record daily costs for each item used. I have a seperate summary sheet which I want to show that days cost for an item and a cumulative total for each item. I have tried VLookUp but cannot seem to get it to roll on each day Any help would be appreciated -- JohnM |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary Sheet
My wife says that I expect people to know what I am thinking without me
telling them! When I said send me a sample workbook I meant privately by changing my e-mail address as it says in my signature. (mailinator.com is a spam/virus trap site that holds e-mails for a few hours and then deletes them) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Sandy Mann" wrote in message ... Hi John, I am still having trouble visualising your data. The Items are split into sections, at the bottom of each section there is a total for that day and a cumulative total. Does this mean that you have a label at the bottom of each section saying something like "Total Item 1"? If so then with "Total Item 1" in A2 of your Summary sheet and dates in row 1 from B1 onwards then =VLOOKUP($A$2,Sheet1!$A$1:$J$24,MATCH(B1,Sheet1!$A $1:$J$1,FALSE),FALSE) worked for me. If it will help you can send me a sample workbook with dummy data for confidentiality -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "JohnM" wrote in message ... Sory should have explained more Items are in Col A Dates Run across the columns D,E,F,G etc The Items are split into sections, at the bottom of each section there is a total for that day and a cumulative total. These are what should be shown o the summary Thanks -- JohnM "Sandy Mann" wrote: John, You don't say how your sheets are laid out so I am assuming that dates ate in Column A, Items in Column B and Amounts in Columns C. For today's total for a Item 1: =SUMPRODUCT((A1:A125=TODAY())*(B1:B125="Item 1")*(C1:C125)) For the Cumulative total for Item 1: =SUMIF(B:B,"Item 1",C:C) Note that SUMPRODUCT() cannot use whole columns but SUMIF() can. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "JohnM" wrote in message ... I have a work sheet where I record daily costs for each item used. I have a seperate summary sheet which I want to show that days cost for an item and a cumulative total for each item. I have tried VLookUp but cannot seem to get it to roll on each day Any help would be appreciated -- JohnM |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary Sheet
JohnM wrote:
I have a work sheet where I record daily costs for each item used. I have a seperate summary sheet which I want to show that days cost for an item and a cumulative total for each item. I have tried VLookUp but cannot seem to get it to roll on each day Any help would be appreciated Hi John, try with SUMIF function or with SUMPRODUCT. If you don't give us some more details about your data and/or your workbook it's quite impossible to say you more... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summary Sheet
Just to round off the thread here is the reply I got from John privately:
Start of John's Reply ************************************************** ** Thanks for the prompt reply it is most kind I think I have come across the solution I followed what you said and whilst doing so I noticed that the Report Number on the Summary sheet kept getting highlighted - this is set at "1" Along the top of the details sheet there are Report Numbers - I changed the number on the Summary sheet to a random number (28) and hey presto the summary sheet was completed - simple if you know how. Problem is nobody told us the easy way John ************************************************** * End of John's reply -- Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Franz Verga" wrote in message ... JohnM wrote: I have a work sheet where I record daily costs for each item used. I have a seperate summary sheet which I want to show that days cost for an item and a cumulative total for each item. I have tried VLookUp but cannot seem to get it to roll on each day Any help would be appreciated Hi John, try with SUMIF function or with SUMPRODUCT. If you don't give us some more details about your data and/or your workbook it's quite impossible to say you more... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic udates on a summary sheet | Excel Discussion (Misc queries) | |||
Displaying information (contained in defined names) on a summary sheet, in different row numbers? | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linking References from Multiple Sheets to One Summary Sheet | Setting up and Configuration of Excel | |||
Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) |