Bob Phillips has a detailed explanation on this usage of Sumproduct, which I
would recommend reviewing.

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

Using my example for counting:
=SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe"))

If you have:

B C
123 Bob
456 Joe
123 Joe
34 Frank
0 Estelle
123
123 Joe
23 Joe
Harry
19 Debbie


It will test the first column for 123 and the second column for "Joe". This
conditional comparison will return arrays of TRUE or FALSE

TRUE FALSE
FALSE TRUE
TRUE TRUE
FALSE FALSE
FALSE FALSE
TRUE FALSE
TRUE TRUE
FALSE TRUE
FALSE FALSE
FALSE FALSE

Excel stores TRUE as 1 and FALSE as 0 (TRUE/FALSE is just what is displayed
on screen for our benefit). When you perform a math operation on boolean (ie
TRUE/FALSE) values, they are coerced to their underlying values (1 or 0).
One way of coercing these values is w/ the double unary operator (double
negative). So the arrays are coerced to:


1 0
0 1
1 1
0 0
0 0
1 0
1 1
0 1
0 0
0 0

Sumproduct then multiplies the arrays:

0
0
1
0
0
0
1
0
0
0

and adds up these results to arrive at 2.



"MC" wrote:

I tried your formula before and it worked but I don't understand why
sumproduct works to count up stuff rather than multiply it. Also, the two
columns I'm checking are both numerical as opposed to your example of 1
column being numeric and 1 column being text. I had tried everything to get
an array function to work, but couldn't so was VERY glad to find your
response...but can you explain why it works and why it won't work without the
--? Thanks!!!!

"JMB" wrote:

Array formula is the term I believe you are looking for and they require
Cntrl+Shift+Enter (CSE).

=SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe"))
would count the number of times 123 appears in B1:B10 where the same row in
column C has the name "Joe".

In this case, CSE is not required as Sumproduct accepts array arguments.

More on Sumproduct, Array Formulae, and multiple condition tests:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.cpearson.com/excel/array.htm


"Russell Seguin" wrote:

I can't remember how to do a multiple criteria "countif" query. I need to
count the occurrances of times when a specific entry in column B is followed
by a different specific entry in the same row in column C. I can't recall
what you call this (in order to use Excel's Help), but I think you had to
simultaneously press Alt-Shift-Enter to make it work...