On Mon, 25 Aug 2008 22:31:11 +0100, "Sandy Mann"
wrote:

"Craig" wrote in message
...
Thanks! I tried it on my example and it works. However, I do not
understand
how the code works

Bob Philips explains how to use SUMPRODUCT() like this far better than I
ever could:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I was just trying with an example. I need to eventually figure out a long
couple of columns such that one must contain a 10 and the other either a
10
or a 5. It could be 10,10 or 10, 5 or 5,10. Any of these results
registering as a one then summed up. Any thoughts?

In SUMPRODUCT() formulas * (multiplication) works like an AND() and + works
like on OR so it follows that

=SUMPRODUCT(((A1:A10=5)+(A1:A10=10)),((B1:B10=5)+ (B1:B10=10)))

will return a count of the number of matches or 5 or 10. Note that this
will also include 5 & 5, if that is not acceptable then read Bob's site or
post back.


The statement that "+ works like OR" is not generally true, only if
the two (or more) conditions are strictly disjoint.
If they are partly overlapping, as can be the case if variable data is
involved then you have to be careful.

Example:

(A1:A10=5)+(A1:A10X) in a formula like the one above will give wrong
results if X, that can be a cell reference, holds a value less than 5.

To get a real OR functionality you can write the above as
- -( ( (A1:A10=5) + (A1:A10X) ) 0 )

Lars-Åke