Grouping data and pivot tables

A

allie357

The first spreadsheet shows departments and their spending
The second spreadsheet calculates total spend by departments over 100k
and some percentages.
I need to be able to count and sum how many transactions there are per
department over 100k. Is there a formula and/or a way to do this with
a pivot table and a formula? I need it to look like the format in the
second spreadsheet. My current formulas are as follows

Column B =SUM(G2:G164) in a hidden column to calculate the number of
contracts over 100k

Column C =B2/$G$912 to calculate % of total

Column D running total for cummulative percentages (formula)

Column I =SUM(H3:H66) in a hidden column to calculate the total spent
per department. There is a separate sum of each department. Is there
an easier way to calculate this?

Column J =I2/$I$914 to calculate % of total
Column B =SUM(G2:G164) to calculate the number of contracts over 100k

Column C =B2/$G$912 to calculate % of total

Column D running total for cummulative percentages (formula)

Column I =SUM(H3:H66) to calculate the total spent per department

Column J =I2/$I$914 to calculate % of total

Column K running total for cummulative percentages (formula)

Is there an easier way to do this with formulas and or pivot tables?

Any help is appreciated. I am sort of new to pivot tables. If this
can be done easier with a pivot table, I would like to know. I am
also using excel 2007, by the way. Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top