I recieved this email from my pal Simon:
“One of the problems in many of the spreadsheets and potential spreadsheets I come across is the need to automatically adjust to changing areas of external data. It seems at first glance that, to refer to a single column in a block of external data, the intersection operator would be useful e.g. =SUMIF(A:A NorthwindInv,A2,C:C NorthwindInv) where ‘NorthwindInv’ is the external data range name. I haven’t seen this much used in practice or indeed written about. Is there some good reason why it’s a bad idea?”
Hmm, the thing is, that style of referring to a ‘Range within a Range’ is new to me. Anyone have any thoughts to share?
Yes, I like this approach, especially with named columns (and rows) instead of just range addresses.
In Excel 2007 the new “structured references” for Tables (http://msdn.microsoft.com/en-us/library/bb833052(office.11).aspx) is really fantastic for this.
I’ve seen newsgroup responses using
=SUMIF(INDEX(TableRange,0,1),criterion,INDEX(TableRange,0,3))
Wouldn’t that produce the same result? If so, the question is which is ‘better’. INDEX uses a nested function call level. On the other hand, intersection syntax is harder to read (IMO).
The INDEX formula may be more flexible in the sense that the column arguments could be expressions themselves while intersection terms A:A and C:C are constants.
Then there’s recalc speed, which in this case means whether intersection operations return range references more quickly than INDEX function calls.
Hey fzz:
I never knew you could use INDEX with a zero parameter to return entire rows or cols in a range – that’s really nifty! I’ll have to remember that one.
Also, I like the idea of the intersection method, but honestly – I never think to use it when building a spreadsheet.
I use named formulae in array formulae all the time. I hadn’t thought of the intersection idea though; I’ll have to give that some thought.
The only drawback I can see is the need to be careful about sizes when more than one name is involved, but most people who are clued-up enough to understand Control-Shift-Enter probably get that.
Speaking of good/bad ideas, SUMIF() and COUNTIF() (and anything else …IF() that requires a string construction and evaluation across an array) are definitely on my “bad idea” list. So very slooooooww.
i’ve always used offset to pull out bits of a named range, something like:
=SUMIF(OFFSET(data,0,0,,1),”A”,OFFSET(data,0,2,,1))
but i like the INDEX formulation. I think you can drop the zero to (works for me anyway)
=SUMIF(INDEX(data,,1),”A”,INDEX(data,,3))
and i think the INDEX way has the benefit of being non-volatile as well?
Mike,
I’m also a SIMIF hater – I use
{SUM(IF(INDEX(Table1,0,1)=criterion,INDEX(Table1,0,2),0))}
as an array formula. Do you think that it is faster than SUMIF?
SUMIF and COUNTIF (and SUMIFS in Excel 20007) are usually quite a lot faster than the equivalent array formulae.
I tend to try and avoid full-column references because of concerns about performance, and INDEX is very fast so thats my preference.
I like using implicit intersection with range-names representing ranges, but I rarely use explicit intersection in Formulae.
AlexJ Faster or not, the array formula is difficult for others to use and makes for a messy and often confusing formula for the less experienced. I would avoid an unnecessary Array Formula for the sake of the maintenance guy.
They also create a real pain when inserting rows/columns. I do use them, but sparingly, it can cause so many problems, just like “Merged Cells”.
As for the intersection, this is of limited value and again very confusing for the reviewer, the simple Index and Sumif of fzz is so easy to understand that I would say it wins my vote.
Speaking of Fast and Slow…I recently did some speed tests using a Timer available from Ross’s website (methods in excel)
I am a bit suprised at the results – As I always thought the Sumproduct would win over Array entered Sum – do others have a different trend
Data 65535 rows – Name, City, Person, Amt
a)Array Entered Sum : 0.584 s
b)Sumproduct :0.825 s
c)Dsum-Sum : 0.223
d)SumIFS(2007): 0.046 s
e) Index/Match :0.05 s
f) VLookup :0.087 s
g) Match (1 Type, Data in Unsorted order, Search item present in the last row) : 0.001s
g) Vlookup (True, Data unsorted, Search item present in the last row) : 0.005 s
Wouldn’t the following array formula work?
{=sum((AA NorthwindInv=A2)*(C:C NorthwindInv))}
I almost always use something like this instead of SUMIF. Which is faster in Excel 2003?