Speed problem with SUMIF

[MoreSpeed]

Afternoon,

I currently have a large file... over 400 columns and over 25,000 rows... and to produce the required report from this data I am using a series of array SUMIF formulae but they are too slow.

When I make the required changes to the file the recalculation time is around 8 minutes, not good when you need to use the file within a meeting or something similiar.

I was wondering if anyone knew of a quicker and more efficient formula I would be able to use??

I am using the following formula (or a variation of) at the moment:

Please Login or Register  to view this content.

Hope you can help me...

Thanks

[EdMac]

Try SUMPRODUCT

see

http://www.xldynamic.com/

[MoreSpeed]

I'll give it ago... Thanks!

[JBeaucaire]

How many SUMIF formulas? How many of these formulas are doing the same thing? The sample formula you posted has 60,000 calculations being done, and that's one cell.

Can you organize the data in a preliminary way so that some of the data is summarized in a single cell you can refer to directly instead of each cell doing the 60,000 calcs?

For instance, a chart somewhere that organizes the data you're going to need into months.

May-09 Total =YourFormulaAbove

And anytime you need May09 figures you simply include that single cell?

[EdMac]

Try like this, I simplified the ranges so you will need to correct them for your circumstances

=SUMPRODUCT((Data!$E$3:$E$29>=39934)*(Data!$E$3:E$29<=39964),Data!$E$3:$E$29)

[Richard Schollar]

A pivot table would be an obvious suggestion.

SUMIF would probably be a more efficient alternative too:


=SUMIF(Data!$EF$3:$EF$29999,">=39934",Data!$EM$3:$EM$29999)-SUMIF(Data!$EF$3:EF$29999,"<=39964",Data!$EM$3:$EM$29999)

Richard

[MoreSpeed]

Errr... in total there are over 6,000 of these formulae in the file, that could be the problem!?!?!?

There are all produce different reports for the powers that be!!

I'll give the suggestions a go and see how I get on.

Thanks All

[EdMac]

Use two spare cells to enter the variable max and min numbers and then put these cell refs in your formula.

That way your formula stays constant.