< question for multiply cells.
 

I'm trying to but together a formula that will take a score and assign a
value of one if the cell is greater then the other cell but from a series of
cells.

Example:

(D10) = 25 & (F10) = 18
(D11) = 20 & (F11) = 25
(D12) = 14 & (F12) = 16


So I want the formula to assign a value of 2 to cell (F14) for the two
higher scores and a value of 1 to cell (D14) for the one higher score. I
also want to assign a value of 2 (D15) for the two lower scores and a value
of 1 (F15) for the other lower score.

I think it would be something like the following but I just can get it to
work:

=COUNTIF(D10F10)+(D11F11)+(D12F12)
=COUNTIF(D10<F10)+(D11<F11)+(D12<F12)

Thanks

< question for multiply cells.
 

Try these:

The equivalent of:

=COUNTIF(D10F10)+(D11F11)+(D12F12)

Would be:

=SUMPRODUCT(--(D10:D12F10:F12))

The equivalent of:

=COUNTIF(D10<F10)+(D11<F11)+(D12<F12)

Would be:

=SUMPRODUCT(--(D10:D12<F10:F12))

--
Biff
Microsoft Excel MVP


"Robert" wrote:

I'm trying to but together a formula that will take a score and assign a
value of one if the cell is greater then the other cell but from a series of
cells.

Example:

(D10) = 25 & (F10) = 18
(D11) = 20 & (F11) = 25
(D12) = 14 & (F12) = 16


So I want the formula to assign a value of 2 to cell (F14) for the two
higher scores and a value of 1 to cell (D14) for the one higher score. I
also want to assign a value of 2 (D15) for the two lower scores and a value
of 1 (F15) for the other lower score.

I think it would be something like the following but I just can get it to
work:

=COUNTIF(D10F10)+(D11F11)+(D12F12)
=COUNTIF(D10<F10)+(D11<F11)+(D12<F12)

Thanks

< question for multiply cells.
 

Robert,

Use

=SUMPRODUCT((D10:D12F10:F12)*1)

and

=SUMPRODUCT((D10:D12<F10:F12)*1)

HTH,
Bernie
MS Excel MVP


"Robert" wrote in message
...
I'm trying to but together a formula that will take a score and assign a
value of one if the cell is greater then the other cell but from a series
of
cells.

Example:

(D10) = 25 & (F10) = 18
(D11) = 20 & (F11) = 25
(D12) = 14 & (F12) = 16


So I want the formula to assign a value of 2 to cell (F14) for the two
higher scores and a value of 1 to cell (D14) for the one higher score. I
also want to assign a value of 2 (D15) for the two lower scores and a
value
of 1 (F15) for the other lower score.

I think it would be something like the following but I just can get it to
work:

=COUNTIF(D10F10)+(D11F11)+(D12F12)
=COUNTIF(D10<F10)+(D11<F11)+(D12<F12)

Thanks