Sumif and IF ???

[Neophyte]

I want to look up sales data from previous years and compare them to this
year's data. I have 4 worksheets worth of sales starting in '04 to present.
Each sheet has the same number of columns: customer number, invoice number,
invoice date and amount, but the rows are different for each year (9705 rows
are the most so far). I have a customer sheet with name, customer number and
salesperson.

This is what I want to do:
1. Type in a date or date range to lookup by sales date for each year's
worksheet in the customer worksheet.
1a. Something like IF the dates are >= 1/1 and <= 1/31 then do sumif() based
on customer number.
2. Return the sales data based on the range above to the correct customer
for each year in the customer sheet.

This way I can see the purchase trends.
I don't know anything about macros or VBA and I am afraid this is going to
lead me into them, down that road of perdition of staying up late with some
of y'alls books in bed. I have done data filters in the past and then used
sumif() to get the information in the past but it is getting old in cutting
and editing formulaes.

Many thanks to the vast unpaid resources here.
Lee C.

 

[tylagusim]

Lee,

In your late-night reading, you might look into Excel's Pivot Table
tool. It's great at consolidating data, e.g. sales people or dates. If
you want to look at trends by month, you may have to create a month
columns in the source datasheets (e.g. "=MONTH(A1)" ). Depending on
how fluent you want to become in VBA, you could put button on the
worksheet which reads your selectrion criteria from a cell (or cells)
on the worksheet and (re)creates the PivotTable for you on demand
using the then-current criteria. This may be more than you want to
bite off, but it sort of depends on how often you do this kind of
thing and how often your criteria change.

/ Tyla /