Bernd P said:
Hello Bob,
Right, I pushed my view without mentioning formulae to extract unique
values because SUMPRODUCT is mostly being offered without doing so
(did you do so here?).
Of course I did, because the OP asked for the solution and gave no mention
to the fact that he needed to extract the unique values to tabulate the
results. If he had I would have covered that aspect as well, but as he
didn't ...
But you have a point here. I am thinking about showing four approaches
now:
1. Fomula extraction and SUMPRODUCT for *very* small lists.
My answer to that is that it depends. Lots of SPs on large tables will be
slow, and it is better that users are aware of this. However, the VAST
majority of users I would contend use SP in very limited scenarios, and it
is not an issue. They not to solve a problem and SP Most often does this
very necessarily.
2. My UDF's Pfreq and Sfreq for a wider range of lists (if a user is
able and willing to use VBA).
Exactly, if they are able and willing. Many organisations preclude the use
of same, many users are scared by it - goodness knows why, but that is life.
3. Pivot tables for more complex lists/statistics.
I agree, but often the source data needs priming for pivots. Pivots are very
good, they are not great.
4. Database approach for problems of greater range / complexity.
Again I agree, especially when combined with pivots, but I am professional
developer. This would scare the bejeebers out of most of the posters here
who are offered SP solutions.
I would be glad if you could turn your "Maybe" into an explicite
opinion: yes, no, or perhaps (when?)...
No, it is a maybe and always will be in my view.
SP is extraordinarily versatile (actually, it is nothing to do with SP, it
is the ability to construct multiple condition tests, they could all be just
as easily deployed in array entered formulae), and provides an excellent
solution for 99.99% of the times in which they are offered (obviously that
is my conjecture, not a fact).
But, anyone who is going to use any array formula, and SP is just an array
formula it is just not array entered, should be aware of the difficulties
that can arise, and they should be aware of the potential solutions (helper
columns, VBA, etc.).
I would hope that anyone who does get an SP solution and then encounters
problems would either do some research into the issue, or ask some more and
learn the tings that we know implicitly (isn't it amazing how much hard work
and experience goes into implicit knowledge <g>), and develop their skills
accordingly. But I would be willing to bet these would be few and far
between.
I know array formulae can give rise to problems when used in the wrong
situations, but what can't (hundreds of pivots all with separate caches,
badly designed databases, linked workbooks, shared workbooks, ... I could go
on).
I think it is disingenuous to make the sort of sweeping statements that you
did. In fact, on writing this, I have changed my view, it is not a maybe, it
is a definite no. SUMPRODUCT, or in actuality the usage in the way that
SUMPRODUCT has evolved, is a very useful function.
To finish, I also disagree strongly with a statement you made in your SP
Pros. You said, ... You do not need Excel's © worksheet functions COUNTIF or
SUMIF. Actually, you should not (never!) even use them because you might
need to increase the number of conditions - and they only support ONE
(condition) ... This is very bad advice in my view, users should be aware of
as many tools at their disposal as possible, and use the correct one (I
prefer to use a screwdriver with screws rather than a hammer). Failure to do
so inevitable leads to abuse of the few tools that they do know, giving rise
to exactly the sort of problems that you hang on SUMPRDUCT.
