Pivot Table & Grand Total Row & XL 2003

[Kevin H. Stecyk]

Hi,

I have a small Excel database with the following columns:

A) Transaction Date (TD)
B) Status (can be either Complete, Dead, or Pending)
C) Commitment Fee (CF)
D) Administration Fee (AF)
E) Downpayment Fee (DF)
F) Legal Fee (LF)

I have created a small pivot table.

Along the row: Status
Along the column: Transaction Date

Data: Commitment Fee, Admin Fee, Downpayment Fee, and Legal Fee

I have further grouped Transaction Date into quarters and years.

I have added to the "Years" the Sum by using Field Settings. So now I have
the sum of each of the fees per year.

My question is, how can I get the Grand Total for all the fees per year?

Any advice is most appreciated.

Kevin

 

[robert111]

suggest you interrogate your raw data using sumproduct, it will look
something like

=sumproduct((a1:a100="2006")*(b1:b100="Q1")*(c1:c100))

assumes years in col A, quarters in col B, fees type 1 in col C

Then add a similar formula for fees type 2 etc etc

Instead of putting 2006 in formula, you can refer to col and row
headings in the summary table you are constructing