Filtering using a formulea

  • Thread starter Thread starter Viscount
  • Start date Start date
V

Viscount

I have got 3 columns

A = Date
B = Organisation
C = Payment

I need to discover the amount paid to each organisation
for a given month. This information will then need to be
automatically copied to a seperate worksheet.

I cannot use Autofilter / Autosum as new data will need to
be added at a later date - so I would like to beable to
insert a formulea that will automatically insert the
correct amount in the seperate spreadsheet.

I am finding the SUMIF Function useful for Columns B&C -
but it does not include the DATE limit that I need to set.

Any ideas..?
 
Use a Pivot Table
1. select your data
2. Data > PivotTable and PivotReport Chart
3. PivotTable wizard appears Step 1 of 3
a. Microsoft Excel list or database selected
b. PivotTable selected
4. Next on wizard
5. PivotTable wizard appears Step 2 of 3
a. accept range or browse to range
6. Next on wizard
7. PivotTable wizard appears Step 3 of 3
a. New worksheet selected
8. Finish
9. Template in excel sheet and field list appear
10. drag fields
a. Date to Column field area
b. Org to Row field area
c. Amount to data area
11. Right Click on the grey Date drop down
12. Group and Show Detail > Group > Select months from popup

results

Sum of Amount Date
Org Aug Sep Grand Total
name of org 10 20 30
name of org 30 40 70
Grand Total 40 60 100

search on pivot tables for more info and options.

KLS
 
Back
Top