I think if you added a helper column you could do it with a SUMIF (which is
fast)

The helper column (might replace DI?) looks like
=if(AND($B3="PEOPLE",DI3<0),1,0)
so that you get a column of 0s and 1s

then the SUMIF looks like
=D1764/SUMIF($A$3:$A$1200,"=" & $A764,helper column)

Then you could either add another column
(=IF(ISERROR(thesumif),0,thesumif) )or use the UDF trick (using another
column would be faster).

The workbook will get larger, but it will calculate a lot faster.

regards
Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Pausert of Nikkeldepaiin"
m wrote in message
...
The spreadsheet captures monthly costs across different categories.
Columns
A-H provide the specifics for each cost record: Who generated it, what
program it was for, what department, etc. Columns I-W represent each month
from October 07 (Column I) through December 08 (Column W) and are where
users
put the actual cost amounts, and that's where the trouble really comes in.
Each of these columns has a variation on this formula:

=IF(ISERROR(DI764/SUMPRODUCT(($A$3:$A$1200=$A764)*($B$3:$B$1200="PEO PLE")*(DI$3:DI$1200<0))),0,DI764/SUMPRODUCT(($A$3:$A$1200=$A764)*($B$3:$B$1200="PEO PLE")*(DI$3:DI$1200<0)))

What this is doing is aligning user's names (Column A) with the kind of
cost
we specifically need to track (in our company's dippy parlance, "PEOPLE"
costs, as opposed to "PO" or "CER" costs, which we are not crunching
here),
and then matching these arrays with a helper column (DI) where costs are
adjusted for each row. The division you see going on is due to the fact
that
the costs in the month breakouts are adjusted by the number of different
records generated for each person per month, and then the whole thing is
error-trapped to keep bogus errors from generating when the divisor is
zero.

Probably my summary isn't very good because I'm a mere tool of the machine
and not an actual financial analyst, but I hope I've given you the idea.
It
actually works and puts out useful numbers.

The problem is that this currently runs to 1200 rows, and worse, as we
move
into the new fiscal year, I just KNOW my bosses are going to want to add
more
monthyly buckets for 2009 and 2010, so this problem will just get worse.
I've
tried taking off the error-trapping, since essentially that means each
formula is being calculated twice, which blows, but my supervisors found
the
error messages unacceptable.

If I could get the PivotTable to mimic the SUMPRODUCT stuff going on, all
would be solved, but I can't. I'm sure the indomitable Captain Pausert
could
figure it out, but I'm just a poseur using his name.


"Charles Williams" wrote:

What does the SUMPRODUCT formula look like?


Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Pausert of Nikkeldepaiin"
m wrote in message
...
I was afraid of that. I've tried trimming the formulas as much as
possible,
but I can't get around a series of SUMPRODUCT formulas that appear in
many
columns and over a few thousand rows. It's just a monster. I've gotten
a
PivotTable on another sheet to do a lot of the work that the formulas
in
the
sheet itself were doing, but I can't figure out how to get rid of the
SUMPRODCTS themselves or to get the table to do what they do.

It's good to know that someone else has read "Witches of Karres,"
though,
so
my day isn't totally depressing!


"Charles Williams" wrote:

Witches of Karres?

AFAIK its not possible to do that.

You might want to look at http://www.DecisionModels.com for some ideas
on
how to speed up Excel calculation. 20-30 minutes is way too long.

regards
Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Pausert of Nikkeldepaiin"
m wrote in message
...
I know that the status bar indicates what percentage of cells have
been
calculated. We have a couple of very large spreadsheets, however,
that
can
take 20-30 minutes to recalculate. Is it possible to use VBA to show
graphically what the status bar shows in tiny print? Several of our
users
have complained that they want progress to be clearly and largely
displayed--apparently looking at the staus bar is too hard.

I see lots of progress meters out there for monitoring the progress
of
a
running macro, but nothing that shows the progress of non-VBA-based
calculation. Is this even possible?