What if my first criteria is matching the value in one column to an array and
the second criteria is matching to a set string, say "Yes")?

Here's my data set:
A B
XYZ Yes
XYZ No
ABC Yes
ABC No
LMN No
LMN Yes

Array named "ID"
XYZ
LMN

Now, my formula needs to check if the value in column A is in the array "ID"
AND Column B = Yes, then Add 1 (I believe this is a SUMPRODUCT or COUNTIF
function or a combination).

I got some help and figured out how to use a COUNT(MATCH) combo to check
column A to see if it's in the array, but can't figure out how to use it in a
multiple condition function.


"JMB" wrote:

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...