mirskman,
You have 3 columns of data (Company Name, Dollar Figure, Date). What you
want is a table that sorts through all the info and comes out nice and clean
and shows that dollar figure by Company per Quarter. What you need to get to
that table is another table (either on the same sheet or a different sheet
that tells us what each quarter responds to. It may look like the following:
Q1 3/1/08 5/31/08
Q2 6/1/08 8/31/08
Q3 9/1/08 11/31/08
Q4 12/1/08 2/28/09
Now in the table you want, you have the companies listed on the left and the
Quarters listed on the top. Since you have multiple years of data I would
suggest labeling each quarter as 2008 Q1 vice just Q1. Either way the
formula below will work. If the cell pivots at cell A1 then enter the
following data in cell B2:
=SUMPRODUCT((--($A2=CompanyNameData))*(DateData>=INDEX(QuarterStartDates,MATCH(B$1,Quarters)))*(DateData<=INDEX(QuarterEndDates,MATCH(B$1,Quarters))),(DollarFigureData))
Where the following apply:
CompanyNameData is the Name of the array that refers to the company column
in the data given
DollarFigureData is the Name of the array that refers to the dollar figure
column in the data given
DateData is the Name of the array that refers to the date column in the data
given
Quarters is the Name of the array that refers to the Quarter in the table
above
QuarterStartDates is the Name of the array that refers to the Quarter Start
Date in the table above
QuarterEndDates is the Name of the array that refers to the Quarter End Date
in the table above
The formula above is an array formula and must be inputted using
Ctrl-Shft-Enter when done. Next Drag over to cover all Quarters, then drag
down to cover all Company Names. This will add up the data as well in case
there's more than one entry per company per quarter. You can also be
creative when Naming your arrays so that it automatically updates when new
data is inputted or when new quarters are added, but that's a different topic.
Let me know if you don't understand anything on here.
Yours,
