Search multiple cells with conditions, sum and auto populate!

[Guest]

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

 

[Guest]

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

---

 

[Guest]

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

 

[Guest]

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