|
Search multiple cells with conditions, sum and auto populate!
I am hoping that this will be an easy question to answer. Please note that I
am familiar with Excel but I do not have much experience with involved formulas. I want to create a formula that allows me to search my spreadsheet for specific entries that were made in a certain month and sum the totals and auto populates a separate report. Relatively straight forward! Scenario: Search for all entries in €śJul 2007€ť called €śPaper€ť and total the sales and drop the sum into the Annual Report. Data Table Date Item Sales 05 Jul 2007 Paper 20.00 07 Jul 2007 Ink 10.00 20 Jul 2007 Pallets 40.00 21 Jul 2007 Paper 20.00 25 Jul 2007 Ink 10.00 29 Jul 2007 Pallets 40.00 Annual Report Item Jul Aug Sep Total Paper 40.00 40.00 Ink 20.00 20.00 Pallets 80.00 80.00 Total 140.00 140.00 I hope some can help me with this. Thank you |
Search multiple cells with conditions, sum and auto populate!
One way is to try a pivot table (PT) It's easy n fast to set up, needs
only a few clicks and drags n drops. And you'd get both the unique listing of items and the sum of corresponding sales by month in a matter of seconds. Here's how .. Col headers in A1:C1 : Date, Item, Sales data running in row2 down Select a cell within the table Click Data PivotTable .. . Click Next Next. In step 3 of the wizard, click Layout. Drag n drop Item within the ROW Drag n drop Date within the COLUMN area, Drag n drop Sales within the DATA area. Click OK Finish. Then hop over to the PT sheet. In the PT, just do a right-click on any date choose "Group and Show Detail" Group. In the Grouping dialog, select By "Months" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Desperately seeking hammer !!" wrote: I am hoping that this will be an easy question to answer. Please note that I am familiar with Excel but I do not have much experience with involved formulas. I want to create a formula that allows me to search my spreadsheet for specific entries that were made in a certain month and sum the totals and auto populates a separate report. Relatively straight forward! Scenario: Search for all entries in €śJul 2007€ť called €śPaper€ť and total the sales and drop the sum into the Annual Report. Data Table Date Item Sales 05 Jul 2007 Paper 20.00 07 Jul 2007 Ink 10.00 20 Jul 2007 Pallets 40.00 21 Jul 2007 Paper 20.00 25 Jul 2007 Ink 10.00 29 Jul 2007 Pallets 40.00 Annual Report Item Jul Aug Sep Total Paper 40.00 40.00 Ink 20.00 20.00 Pallets 80.00 80.00 Total 140.00 140.00 I hope some can help me with this. Thank you |
Search multiple cells with conditions, sum and auto populate!
My first attempt at a PT. I'm nearly there...... When I drop the Sales col
into the pivot table it displays as the total number of entres IE 2 for Paper. Ideally I need this to display the total of the combined sales of ÂŁ40.00. Thank you for such a speedy reply I am really grateful. Michelle "Max" wrote: One way is to try a pivot table (PT) It's easy n fast to set up, needs only a few clicks and drags n drops. And you'd get both the unique listing of items and the sum of corresponding sales by month in a matter of seconds. Here's how .. Col headers in A1:C1 : Date, Item, Sales data running in row2 down Select a cell within the table Click Data PivotTable .. . Click Next Next. In step 3 of the wizard, click Layout. Drag n drop Item within the ROW Drag n drop Date within the COLUMN area, Drag n drop Sales within the DATA area. Click OK Finish. Then hop over to the PT sheet. In the PT, just do a right-click on any date choose "Group and Show Detail" Group. In the Grouping dialog, select By "Months" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Desperately seeking hammer !!" wrote: I am hoping that this will be an easy question to answer. Please note that I am familiar with Excel but I do not have much experience with involved formulas. I want to create a formula that allows me to search my spreadsheet for specific entries that were made in a certain month and sum the totals and auto populates a separate report. Relatively straight forward! Scenario: Search for all entries in €śJul 2007€ť called €śPaper€ť and total the sales and drop the sum into the Annual Report. Data Table Date Item Sales 05 Jul 2007 Paper 20.00 07 Jul 2007 Ink 10.00 20 Jul 2007 Pallets 40.00 21 Jul 2007 Paper 20.00 25 Jul 2007 Ink 10.00 29 Jul 2007 Pallets 40.00 Annual Report Item Jul Aug Sep Total Paper 40.00 40.00 Ink 20.00 20.00 Pallets 80.00 80.00 Total 140.00 140.00 I hope some can help me with this. Thank you |
Search multiple cells with conditions, sum and auto populate!
If it appears as "Count of Sales" instead of "Sum of Sales" when you dropped
Sales into the DATA area, probably the Sales figs in your source table are text numbers (instead of real numbers). Try this to convert it to real numbers. Copy an empty cell, then right-click on the Sales col (in the source table) Paste special Add OK. Then try re-doing the pivot again. Alternatively, select any cell in the PT that you've done, right-click Refresh data. Then right-click on "Count of Sales" in the PT Field Settings. Select "Sum" under "Summarize by" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Desperately seeking hammer !!" wrote: My first attempt at a PT. I'm nearly there...... When I drop the Sales col into the pivot table it displays as the total number of entres IE 2 for Paper. Ideally I need this to display the total of the combined sales of ÂŁ40.00. Thank you for such a speedy reply I am really grateful. Michelle |
| All times are GMT +1. The time now is 05:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com