"PRODUCT" implies multiplication to me,
but that does not appear to be the case here.

Yes, that's what's happening. Here's how it works...

Let's use a small data sample:

...........B..........C..........D
2......Mon.....Tues.....Thurs
3.......x...........x...........x

=SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x"))

=2

These expressions will return arrays of either TRUE or FALSE:

(B2:D2={"Tues";"Thurs"})
(B3:D3="x")

It would look like this:

B2:D2 = Tues = FALSE,TRUE,FALSE
B2:D2 = Thurs = FALSE,FALSE,TRUE
B3:D3 = x = TRUE,TRUE,TRUE

These arrays are then multiplied together:

(B2:D2={"Tues";"Thurs"})*(B3:D3="x")

Performing any math operation on a logical value (TRUE, FALSE) will coerce
the result to a numeric value.

TRUE * TRUE = 1
TRUE * FALSE = 0
FALSE * TRUE = 0
FALSE * FALSE = 0

So, here's how those arrays are multiplied together and the result:

B2:D2 = Tues = FALSE,TRUE,FALSE *
B3:D3 = x = TRUE,TRUE,TRUE =
{0,1,0}

B2:D2 = Thurs = FALSE,FALSE,TRUE *
B3:D3 = x = TRUE,TRUE,TRUE =
{0,0,1}

Now, when the formula calculates this produces one array like this:

{0,1,0;0,0,1}

So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS.

The PRODUCTS are {0,1,0;0,0,1}
The SUM of those PRODUCTS are 0+1+0+0+0+1

So:

SUMPRODUCT({0,1,0;0,0,1}) = 2

=SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x"))

=2

Ok, now let's look at your formula and see why it didn't work:

=COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X")

COUNTIFS does "straight comparisons" only. Using the double unary, you're
attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It
has to be the straight comparison:

=COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X")

However, there's still a probem with that. Since the cells cannot hold both
Tues and Thurs at the same time the result will always be 0. I'm not sure of
the "internal workings" of the COUNTIFS function but it can be explained the
same way as SUMPRODUCT is: array multiplication.

B2:D2 = Tues = FALSE,TRUE,FALSE
B2:D2 = Thurs = FALSE,FALSE,TRUE
B3:D3 = x = TRUE,TRUE,TRUE

But in this case the array multiplication is done a bit differently:

FALSE,TRUE,FALSE *
FALSE,FALSE,TRUE *
TRUE,TRUE,TRUE =
......0........0........0........

--
Biff
Microsoft Excel MVP


"DOUG" wrote in message
...
T. Valko: That worked like a charm. Thanks.

PS, Could you please explain it to me. I looked up the SUMPRODUCT
function
earlier today, based on your examples. "PRODUCT" implies multiplication
to
me, but that does not appear to be the case here. Also, when I tried
COUNTIFS earlier, it worked in one cell and not in another. The version
of
it I showed above should have worked as far as I know. Would you care to
comment?

DOUG

"T. Valko" wrote:

Try it like this:

=SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x"))

What do the paired dashes mean, anyway

They can't be used in the COUNTIFS like you have them.

See this:

http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"DOUG" wrote in message
...
I have a company fitness chart, with "X"s indicating participation on
certain
days of the week. The requestor wants a sub-totaled percentage for
fitness
sessions on Tuesdays and Thursdays (combined) and for Wednesdays, plus
an
overall score. The overall participation percentage is no problem.
For
the
sub-scores, I am using a COUNTIFS function:
=COUNTIFS(--(B2:P2),"TUES",--(B2:P2),"THURS",--(B3:P3),"X"). But, this
produces an error. (What do the paired dashes mean, anyway)? I have
tried
a
couple of other functions, COUNTA, COUNTIF and IF, plus some of the
SUMPRODUCT examples from the discussion page. They do not work as
advertised
either, although I thought I was close at one point. Suggestions are
welcome, as always.
DOUG