Sumproduct as an Alternative to Array formulas.. Substitute of CTRL+Shift+Enter

Hi Vikas,

Nice, and I've seen similar alternatives before, but the key question remains (and is perhaps the reason why such constructions have yet to catch on as a serious contender for the standard CSE version):

Are the three extra function calls (SUMPRODUCT, INDEX and FREQUENCY) compensated for by the fact that it no longer needs to be committed as an array formula?

I went through a phase a year or so ago where I was borderline obsessed with avoiding array formulas, so much so that I would insert sometimes up to two or even three extra INDEX functions into a large formula in order to avoid having to use CSE. Unfortunately the end result was that the new, non-array version had a much heavier impact on resource than the previous array version!

I'm not saying that this isn't a viable solution. It is, and it has some very nice features from which people can learn a lot about Excel functions (the INDEX(FREQUENCY... combination is a nice construction that not many people know about).

That said, I always have a slight worry (I believe Tony Valko shares this view!) that array formulas are commonly misunderstood as something which we should, in general, "make an effort to avoid", a conception that is not helped by the numerous sources around the internet that warn of the "dangers" of using array formulas (an OP once told me, when I asked them why they wished to avoid array formulas in any solution offered to them, that it was because they had heard that they can "damage your computer"!).

Anyway, thanks for sharing the post and the nice ideas: my advice would be to continue these types of investigations into non-array alternatives, but also to perhaps bear in mind that such alternatives may not be any "better" than the equivalent array version!

Regards

You're welcome.

I just hope I didn't put you off investigating into these alternative solutions. One of the things that you notice after a while is that there are many Excel users (advanced included) who seem to have long ago given up on searching for new ideas, and who continue to trot out the same old formulas...

Of course, these "old" formulas are in "standard use" by virtue of their being, in general, the best and most rigorous constructions. But not all, I have to say. And even those that appear to be beyond improvement may not be, if only we look hard enough.

So well done and keep on investigating and looking for new, creative solutions in Excel! I look forward to seeing more from you in the near future!

Cheers

I am really glad to see that a young man(sawing your avatar and thinking that this is your pic) looking for new Excel solutions.

I say this because most of the young people refuse to (even)use Excel for their needs(At least in Greece).

My friend XOR LX told about all the "techical" issues.