Hi, I have 20000 array formulas in an excel sheet. {=SUM(IF(C4:C25000=C4,IF(E4:E25000=E4,AA4:AA25000, ""), ""))}
I am using vba to refesh the columns. Range("A:C").calculate
This takes 8 mins to calculate them. Is there an easy way to either enter and calculate the formula through vba or calculate it through vba which is much quicker?
=SUMproduct(--(C4:C25000=C4),--(E4:E25000=E4),AA4:AA25000) may be marginally faster
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
You could maybe use a Helper column and concatenate columns C and E, for example, =C4&"|"&E4 and copy down. Then use:
=SUMIF(Helper Column, C4&"|"&E4, AA4:AA25000)
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks