=SUMPRODUCT(--(Data!$X$2:$X$9658=$A38),--(Data!$Y$2:$Y$9658=C$8),Data!$Z$2:$Z$9658)
This formula exists in about 30 tabs and the data range will increase every month also makes the file size quite big and slowing down the processing time. Is there a better formula i can use instead?
Last edited by Gti182; 06-17-2013 at 07:50 AM.
You can try with Pivot table but otherwise no (you could have faster SUMIFS in Excel 2007 and newer)
On 2007 or later versions use SUMIFS
=SUMIFS(Data!$Z$2:$Z$9658,Data!$X$2:$X$9658,$A38,Data!$Y$2:$Y$9658,C$8)
which might prove more efficient
Life's a spreadsheet, Excel!
Say thanks, Click *
forgot to add i've now upgraded to office 2010. Will try the sumifs thank you
You could also upgrade your MS-Off version (as you can see from this exmple, that's quite important because it can give different solutions.
Put this formula in AA2:
=X2&Y2
then copy down.
Then you can replace your SP formula with this:
=SUMIF($AA:$AA,$A38&C$8,$Z:$Z)
which should be more efficient, even with full-column references.
Hope this helps.
Pete
great thanks for the suggestions, completely forgot about the concatenation option
Ah, my comments related to Excel 2003, but they are still relevant to XL2010.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks