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

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

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

"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

T.Valko and or Biff: Thank you very much. I understand what your are
saying, but not all of the terminology. Paraphrasing, the formula drives the
product to a number and then sums the number, yes? Also, the COUNTIFS did
not work because it was interpreting criteria to mean "OR" rather than "AND"
and it appears to be hardwired that way - although, you would think
specifying multiple criteria would default to "AND", as in, "if this is true
AND this is TRUE" and so on.

Aside: Speaking of the SUMPRODUCT function, please interpret this
expression...

=SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1

I use it to rank scores all the time and it is great, but I do not know what
the expression means. (I think this came from Biff Biffenden or Gordon
Dibben originally).

Thanks a lot,
DOUG ECKERT




"T. Valko" wrote:

"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

the formula drives the product to a number and
then sums the number, yes?

Yes

the COUNTIFS did not work because it was interpreting
criteria to mean "OR" rather than "AND" and it appears
to be hardwired that way - although, you would think
specifying multiple criteria would default to "AND", as in,
"if this is true AND this is TRUE" and so on.

No, the COUNTIFS works on the logic of *AND*. That's why it didn't work.

B2 = Tues
B3 = x

=COUNTIFS(B2,"TUES",B2,"THURS",B3,"X")

Count if B2 = Tues and B2 = Thurs and B3 = x

B2 can't be both Tues and Thurs at the same time so:

TRUE * FALSE * TRUE = 0

An example of using "AND" logic on the same range is when testing numbers
(or dates/times) to be within a range. For example:

B2 = 10
B3 = x

Count if B2 =0 and B2 <=25 and B3 = x

B2 can be both =0 and <=25 at the same time.

=COUNTIFS(B2,"=0",B2,"<=25",B3,"x")

TRUE * TRUE * TRUE = 1

Speaking of the SUMPRODUCT function, please interpret this expression...
=SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1

That formula produces counts (ranks) from highest to lowest and does it so
that the counts (ranks) are consecutive.

F2 = 110 = 1
F3 = 110 = 1
F4 = 100 = 2

Using the RANK function the results would be:

F2 = 110 = 1
F3 = 110 = 1
F4 = 100 = 3

=SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1

This expression will return an array of either TRUE or FALSE:

(F2<F$2:F$4)

110 < 110 = F
110 < 110 = F
110 < 100 = F

Because SUMPRODUCT sums the products we need to convert those T and F to
numbers. One to do that is through a math operation like we did in the other
SUMPRODUCT formula. In this example we're using the double unary which will
do the same thing:

--TRUE = 1
--FALSE = 0

So this array: --(F2<F$2:F$4) = {0;0;0}

SUMPRODUCT is based on array multiplication. We have the first array:
{0;0;0}, now we need another array to get our result.

We get the second array from this expression:

1/COUNTIF(F$2:F$4,F$2:F$4)

This is also the expression that allows for duplicate numbers to be ranked
the same.

First we get a series of COUNTIFs:

COUNTIF(F$2:F$4,F$2) = 2
COUNTIF(F$2:F$4,F$3) = 2
COUNTIF(F$2:F$4,F$4) = 1

Then we divide these counts by 1. This is what allows the formula to rank
duplicate numbers the same.

1/2 = 0.5
1/2 = 0.5
1/1 = 1

Now we have the second array: {0.5;0.5;1}

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

{0;0;0}*{0.5;0.5;1} = 0

Now, you might be thinking that a result of 0 doesn't make any sense. Since
this example has been evaluating cell F2 which is the highest number in the
range we know that none of the numbers to be ranked will meet this
condition:

(F2<F$2:F$4)

F2 (110) is not less than any number to be ranked. So the first array was
all 0s: --(F2<F$2:F$4) = {0;0;0}.

And we know that an array of all 0s will return 0 as the result of the
SUMPRODUCT function. But, that's why we add 1 at the very end of the
formula:

=SUMPRODUCT(0)+1

So, F2 (110) is ranked 1.

As the formula is copied down each number in the range gets evalauated like
this and we end up with the final ranks of:

110 = 1
110 = 1
100 = 2

--
Biff
Microsoft Excel MVP


"DOUG" wrote in message
...
T.Valko and or Biff: Thank you very much. I understand what your are
saying, but not all of the terminology. Paraphrasing, the formula drives
the
product to a number and then sums the number, yes? Also, the COUNTIFS did
not work because it was interpreting criteria to mean "OR" rather than
"AND"
and it appears to be hardwired that way - although, you would think
specifying multiple criteria would default to "AND", as in, "if this is
true
AND this is TRUE" and so on.

Aside: Speaking of the SUMPRODUCT function, please interpret this
expression...

=SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1

I use it to rank scores all the time and it is great, but I do not know
what
the expression means. (I think this came from Biff Biffenden or Gordon
Dibben originally).

Thanks a lot,
DOUG ECKERT




"T. Valko" wrote:

"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