Hi,

My problem: the SUM IF array formulas I use aldo accurate are too numerous, check too much cells and use many conditions making my computer while recalculating them very slooooow....

The worksheet I work with is usually around 85000 lines long.

Context: I have to calculate the Fill Rate%, Total per Ad Size and per Category by

- ad category (4),

- ad sizes (3)

- per site (around 250+) in one worksheeet.

The calculated cells are the yellow ones in R, S, T, U.

Note: both (4), (3), (250+) listed above are current numbers but they might be more in the very near future.

I need to do those calculations in the worksheet while keeping the current layout as I will use it to do a pivot table of the results afterwards. I wouldn't want to chunck my worksheet in smaller ones and have to merge the data afterwards as value afterwards...

Any way you think that could improve my situation?

--------------------------

-----------------------

My data is segmented by:

Ad sizes: 160x600, 300x250, 728x90

Ad categories: CPM, Remnant, No Cost, Marketing

Site Names: 250+

I have used the following formulas (as arrays) and they are accurate.

1) To calculate the numbers of impressions per Ad Sizes

=SUM(IF($D$2:$D$100000=$D543;IF($V$2:$V$100000=$B543;IF($A$2:$A$100000=A543;$N$2:$N$100000;0);0);0))

2) To calculate the numbers of impressions per Ad Categories:

=SUM(IF($D$2:$D$100000=$D543;IF($V$2:$V$100000=$B543;IF($A$2:$A$100000=A543;$N$2:$N$100000;0);0);0))

3) Fill Rate: S543/T543

A document with some data is attached for your comprehension.

Thanks

20110914-ExpertsExchange-workdoc.xlsm