I'd like to incorporate the INDIRECT function to the formula below:
=SUM(SUMIFS(Items!D$2:D$999994,Items!$A$2:$A$999994,MTD!$B8,Items!$B$2:$B$999994,"REVENUE",Items!$P$2:$P$999994,{9}))
I'd like to replace references to columns D and P with an INDIRECT formula.
Help! Thanks.
Not knowing what you are doing, lets assume that column D is variable length. Then in cell Z1 use:
Then modify your formula, so:
Please Login or Register to view this content.
ETC...Please Login or Register to view this content.
Ben Van Johnson
In SUMIFS each precedent range must have the same dimensions - defined explicitly within the formula.
This is one area where SUMIFS differs to SUMIF in which the dimensions of the first range are automatically applied to the optional range irrespective of any dimensions that may have been applied to that range explicitly, eg:
are both processed asPlease Login or Register to view this content.
* pending XL version this particular construct is Volatile (see: http://www.decisionmodels.com/calcsecretsi.htm)Please Login or Register to view this content.
This automatic resizing does not happen in SUMIFS and you will get #VALUE! errors if the dimensions of each precedent range are not the same.
On an aside if the intention is to have just the one criteria value regards P {9} then you can remove the in line array and outer SUM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks