I'm not following you!

You'll have to explain the whole problem.

--
Biff
Microsoft Excel MVP


"DOUG" wrote in message
...
T. Valko: One more twist. The requestor wants to set the denminator in
the
TUE-THU column to count only Tuesdays and Thursdays instead of counting
all
days. I have played with it to no avail, since I do not speak this
language
yet. I did try plugging in the COUNTIFS function in the denominator and I
did try plugging in part of the numerator from your SUMPRODUCT equation
into
the denominator, but 'no luck. 'Any suggestions?

DOUG

"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"DOUG" wrote in message
...
WOW!

Thanks again, T. Valko (BIFF).

DOUG

"T. Valko" wrote:

Is (F2<F$2:F$4) saying...

It's comparing F2 against every cell in the range F2:F4.

F2 = 110
F3 = 110
F4 = 100

Is F2 (110) less than F2 (110) = no = FALSE
Is F2 (110) less than F3 (110) = no = FALSE
Is F2 (110) less than F4 (100) = no = FALSE

As you drag copy the formula down the column the reference to F2 will
increment so that you get:

Is F3 (110) less than F2 (110) = no = FALSE
Is F3 (110) less than F3 (110) = no = FALSE
Is F3 (110) less than F4 (100) = no = FALSE

Is F4 (100) less than F2 (110) = yes = TRUE
Is F4 (100) less than F3 (110) = yes = TRUE
Is F4 (100) less than F4 (100) = no = FALSE

COUNTIFS function, is there a way to change the
AND interpretation to an OR interpretation?

Not exactly!

A1:A10 = numbers
B1:B10 = whatever

Count where A = 5 *or* A = 10 *and* B = x

=SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x"))

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

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x"))

All 3 of those formulas will do that. So, just looking at those
formulas
which one would think is the best formula to use for this? If you said
the
3rd one you'd be right!

--
Biff
Microsoft Excel MVP


"DOUG" wrote in message
...
T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______
of
the
range F$2:F$4)?

Also, in the COUNTIFS function, is there a way to change the AND
interpretation to an OR interpretation?

DOUG

"T. Valko" wrote:

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.