I wish I could lay some claim to that but I cannot. It is doubtful that
anyone can claim these things absolutely as so many contribute to their
evolution. I first saw it in a more simplistic form

=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)

and it has evolved over the years to what I showed that day.

There are better ones out there IMO, although that does have the appeal of
brevity. I saw one from Domenic a few weeks back that sums in non-contiguous
cells that I really liked

=SUMPRODUCT(--ISNUMBER(MATCH(COLUMN(C1:N1)-COLUMN(C1)+1,{1,3,6,8,12},0)),--(
C1:N1=1))

This sums C1,E1,H1,J1,N1 only. I had a solution to this problem, but not as
elegant.

Here is one of mine for extracting the numbers from a delimited string and
sum them. So if A1 contains 1,12,123 it will get the 1 and 123 out and sum
them

=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(IND IRECT("1:"&L
EN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1)))),
FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(INDIRECT(" 2:"&LEN(A1)-LEN(SUBSTITUTE
(A1,B1,""))+2))))-
FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",ROW(INDIRECT(" 1:"&LEN(A1)-LEN(SUBSTITUTE
(A1,B1,""))+1))))-1))

If you think that all this is useful, take a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
HTH

Bob Phillips

"Ken Johnson" wrote in message
ups.com...
Bob,
Thanks for that, it's going to take me a while to work through it. It's
easily the most amazing bit of excel wizardry I've ever seen. Is it
your own invention?