< > question for multiply cells.

[Robert]

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(D10>F10)+(D11>F11)+(D12>F12)
=COUNTIF(D10<F10)+(D11<F11)+(D12<F12)

Thanks

 

[T. Valko]

Try these:

The equivalent of:

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

Would be:

=SUMPRODUCT(--(D1012>F10:F12))

The equivalent of:

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

Would be:

=SUMPRODUCT(--(D1012<F10:F12))

 

[Bernie Deitrick]

Robert,

Use

=SUMPRODUCT((D1012>F10:F12)*1)

and

=SUMPRODUCT((D1012<F10:F12)*1)

HTH,
Bernie
MS Excel MVP