countif question?

  • Thread starter Thread starter deras
  • Start date Start date
D

deras

i have two columns of numbers... i want to compare the left number wit
the right number on each line of the columns, and see how often th
left column is greater.

how do i do that?

so far i have only been able to get the countif function to work wit
actual numbers - (countif(aj1:aj170,">.5), cell values don't seem t
work.

here are the columns i want to compare - aj1:aj170, ak1:ak17
 
Assuming you want to count the cells in AJ1:AJ170
with numbers greater than each of the number in AK1:AK170

Try putting in say, AL1 : =COUNTIF($AJ$1:$AJ$170,">"&AK1)

Copy down AL1:AL170
 
deras said:
i have two columns of numbers... i want to compare the left number with
the right number on each line of the columns, and see how often the
left column is greater.

how do i do that?

so far i have only been able to get the countif function to work with
actual numbers - (countif(aj1:aj170,">.5), cell values don't seem to
work.

here are the columns i want to compare - aj1:aj170, ak1:ak170
Click to expand...

=SUMPRODUCT((AJ1:AJ170>AK1:AK170)*1)

or
=SUMPRODUCT(--(AJ1:AJ170>AK1:AK170))

or (but this is an array formula so has to be entered using Ctrl+Shift+Enter
rather than just Enter)
=SUM((AJ1:AJ170>AK1:AK170)*1)
 
=SUMPRODUCT(--(AJ1:AJ170>AK1:AK170))

is better worded than the other one and none of them requires confirming
with control+shift+enter.
 
Better worded?


--

Regards,

Peo Sjoblom


akyurek said:
=SUMPRODUCT(--(AJ1:AJ170>AK1:AK170))

is better worded than the other one and none of them requires confirming
with control+shift+enter.

"Paul" <none> wrote in message news:ON8k1Z#[email protected]...
Click to expand...
 
=SUMPRODUCT(--(AJ1:AJ170>AK1:AK170))

worked perfectly, thanks.... i am also using this...

=SUMPRODUCT(--(AJ1:AJ170=AK1:AK170))


is there anyway to get excel to highlight the cells that scored each
count?
 
Select AJ1:AK170 with AJ1 as the active cell, do format>conditional
formatting,
select formulas and put this in the formula box

=$AJ1>$AK1

click the format button and select a highlight colour under patterns, click
OK twice
 
deras said:
=SUMPRODUCT(--(AJ1:AJ170>AK1:AK170))

worked perfectly, thanks.... i am also using this...

=SUMPRODUCT(--(AJ1:AJ170=AK1:AK170))


is there anyway to get excel to highlight the cells that scored each
count?
Click to expand...

You could use conditional formatting.
Select AJ1:AJ170.
Format > Conditional formatting
Change first box to "Formula Is".
Put this formula in the second box:
=(AJ1>AK1)
Press Format, select the format you would like and press OK twice.
 
Back
Top