# Thread: Subtotals & Data (Excel 2002 SP2)

1. ## Subtotals & Data (Excel 2002 SP2)

I'm developing an Excel "report" that utilises a large data set from the mainframe, the process is very manual. I manipulate and sort the data, then subtotal by category. So I can use these category subtotals to poulate the report I have to copy visible cells, find and replace the word "total" with nothing, paste, special values the subtotal values to remove the formula, trim the category code and paste special back the category code before it works with vlookup
Is there a better way to accomplish this or do I have to <img src=/S/grin.gif border=0 alt=grin width=15 height=15> and bear this? I have attached a small example
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>

2. ## Re: Subtotals & Data (Excel 2002 SP2)

Try using array formulae as in the attached version of yopur workbook.

If H2 contains 15151, then th efollowing formula will return 3,999.60

=SUM(IF(\$C\$2:\$C\$604=H2,\$D\$2:\$D\$604,0))

That formula is entered with Ctrl-Shift-Enter so that it is enclosed in brace brackets.

Andrew C

3. ## Re: Subtotals & Data (Excel 2002 SP2)

I think a pivot table would be easier:
- Create a pivot table based on the raw data.
- Use the GETPIVOTDATA function to retrieve data from the pivot table.
See attached zipped version.

4. ## Re: Subtotals & Data (Excel 2002 SP2)

Thank you Andrew, it does simplify matters a lot.
<img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>

5. ## Re: Subtotals & Data (Excel 2002 SP2)

That is a method I had not even considered, that's great, thank you Hans. Certainly makes life easier. <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•