Say your formula is something like =SUMIF(A1:A30000,"A",B1:B30000)
You could also fill B1:B30000 with =IF(A1="A",B1,0) and in C have
=SUM(B1:B30000)

Depending on the number of cells in A and B, one method might be
considerably faster than the other


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Naz" wrote in message
...
Hi Niek,

Thanks for your response, the spreadsheet doesn't have VLookups, (I had
them
banned a while ago !) and I know that its the sumif, as at the moment the
data is 14000 rows, if I changed it to say 7000 then the time halves.
I would be grateful if you could explain what you mean by

A helper column and a normal SUM are probably faster than SUMIF.


How would you use a normal SUM and helper column to achieve this?



_______________________
Naz,
London


"Niek Otten" wrote:

I used FastExcel on a sheet with which I had recalc tome problems.
It helped me spot the problem area in minutes and then it didn't take
much
time (in my case!) to cure the problem.
Summing large ranges is generally very, very fast. A helper column and a
normal SUM are probably faster than SUMIF.
Your problem might be the VLOOKUP. You probably use FALSE as 4th
argument.
This causes Excel to search the range from top to bottom. If you can sort
the data and omit the 4th argument, you may find a great reduction in
calculation time. But you'll have to check yourself that the value to be
searched was indeed the one found (to check for missing data)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Naz" wrote in message
...
Hi all,

I have a spreadsheet that I have been using for the year and now it is
time
to update it. The biggest problem with the old one was the re-calculate
time.
I have 2 worksheets in the workbook, 1 containing data and the other a
List
of employees (over 500). The List sheet picks up the pay of each staff
from
the data sheet every month. I used to use an array formula to do this
then
used a conjoined sumif (Sumif(Range,PayRef&Month&Costcode,Sumrange)
with
helper column as it was faster, but as the Data sheet gets bigger as
the
years goes on the recalculate time is now at 12 secs.

Is there a faster alternative to Sumif?
Is there better method of summing large ranges of data (data sheet
contains
14000 rows)?
Has anyone used the product FastExcel (www.fastexcel.com) to optimise
there
spreadsheets and is it any good?

All help is greatly ppreciated

_______________________
Naz,
London