Count # Times Value of Cell in Range 2 is > Than Value of a Cell in Range 1

[MLCall]

I am using Excel 2010.

I need to count the number of times the cell values in range 2 are greater than the cell values in range 1. I've tried different variations with no luck. I also tried using the one suggested by the software via the Formula/Function option and did not get the expected results. I didn't try COUNTIFS because it I don't have multiple criterias (at least I don't think that I do).
Some of the variations that I've tried:
1. =countif(z2:z23,">",s2:s23)
2. =countif((z2:z23)'>'(s2:s23))
3. =countif(z2:z23,'">s2:s23") Note: This was the formula created by the software based on the words I entered

I'd appreciate any suggestions.

MLCall

[oeldere]

With an helpcolumn (see the attached file).

Please Login or Register  to view this content.

[Pete_UK]

Use the this one:

=countif(z2:z23,">"&s2:s23)

but instead of using <enter> to commit the formula, use the key combination of Ctrl-Shift-Enter (CSE) together to turn it into an array formula. If you do this correctly then the formula will be wrapped in curly braces when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula then you will have to use CSE again to commit the formula.

Another way would be to do this:

=SUM(IF(Z2:Z23>S2:S23,1,0))

again committed using CSE.

Hope this helps.

Pete

[MLCall]

Pete,

I used the SUM option which worked great for me. I have to be honest and say that I don't understand the "1,0" portion of the formula although my predecesor used it in a worksheet that I am maintaining. Bottom line though, I got my expected results.

Kudos!

MLCall