Hello!
I have an array which is doing exactly what I need but it is also drastically slowing my workbook. I'm hoping someone may have an alternative solution which would not hinder calculations.
{=SUM(IF(Circuits!$B:$B="Fiscal '09",IF(Circuits!$A:$A=$A$2,Circuits!BX:BX,0),0))}
I have tried to make an absolute reference to Circuits!$B3:$B$283 & Circuits!$BX$3:$BX$283 but for whatever reason, the formula ceases to function properly.
Many Thanks in Advance!
Try
=Sumproduct(--(Circuits!$B3:$B$283="Fiscal '09"),--(Circuits!$A$3:$A$283=$A$2),Circuits!$BX$3:$BX$283)
which is not Array-Entered (just use ENTER)...
but not sure if it will increase perfomance.. based on how many times the formula is repeated...
Perhaps a Pivot Table might also be appropriate and if so would definitely speed things up drastically.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
deleted
already answered
"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
It works if I manually enter the formula. I altered the appropriate field to be relative and attempted to drag to fill. Although the appropriate field in my formula changed, the result did not.
=SUMPRODUCT(--(Circuits!$B:$B="Fiscal '09"),--(Circuits!$A:$A=$A3),Circuits!BX:BX)
=SUMPRODUCT(--(Circuits!$B:$B="Fiscal '09"),--(Circuits!$A:$A=$A3),Circuits!BY:BY)
Ideally, I need to drag to fill, down 5 rows and across 19 columns. Also, what does the double dash in the formula signify?
Thanks again.
Last edited by smninos; 07-15-2009 at 03:41 PM.
Check that you don't have manual calcs set:Originally Posted by smninos
Go to Office Button and click Excel Options.. Select Formulas from left pane and make sure "Automatic" is set on.
They coerce resulting arrays of TRUE/FALSE to 1/0 so sumproduct can do its math.
Please also update your profile to identify that you are using XL2007 to make it easier for us to come up with suggestions for your version.
Remembered that I had turned auto calc off due to the arrays being so slow. The suggested formula is slightly quicker although not much. I also followed your suggestion about a pivot table but I don't think this will satify the appearance of the data that is needed.
Are these the only options I have? Either way, I really appreciate your help. Thanks
Maybe add 5 helper columns and then use Sumif.... not sure how much faster..
e.g
In Circuits sheet add a column with formula:
=If(AND($B2="Fiscal '09",$A2=Sheet1$A3),"X","")
where Sheet1 is the sheet the A3 is found.
copied down and across 5 columns.
Then use formula:
=Sumif(Circuits!CC:CC,"X",Circuits!BX:BX)
where CC:CC is the first column you added as a helper, copied down and across 5 columns.
Last edited by NBVC; 07-15-2009 at 04:15 PM.
If you're using XL2007 why not SUMIFS ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
... sorry that wasn't right...
... and yes you can use SUMIFS... not sure if that would be faster though...keep forgetting to look at version used.
=SUMIFS(Circuits!BX:BX,Circuits!$B:$B,"Fiscal '09",Circuits!$A:$A,$A3)
Last edited by NBVC; 07-15-2009 at 04:27 PM.
AFAIK: SUMIFS is very efficient ...
Yeah, I don't use SUMIFS much at work since only a few of us have 2007 and I am usually designing sheets for 2003 versions... so I haven't done too much "efficiency" testing.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks