Array formula: count non-zero values in two columns

Hi,

I have two columns that each have numbers and blank cells - column H and column I - and x number of rows. I currently have the array formula (included in another sheet) below to count how many numbers in the first column (column H) are greater than the corresponding numbers in the second column (column I). An example of a result of this formula is that there are 60 instances where the number in column H is greater than the corresponding number in column I.
The only problem is that this array formula also counts the blanks/zero values.

{=(SUM((Sheet1!$I17:$I451<Sheet1!$H17:$H451)*1))}

How may I alter this formula so that it counts the number of numbers in column H that are greater than the numbers in column I, ignoring all blanks? I am using Excel 2010.
(Apologies, I'm very new to array formulas, and endless research online hasn't helped.)

Thanks in advance for your help!

RN

Hi RN, try this array formula...

{=SUM(($I$17:$I$451<$H$17:$H$451)*(NOT(ISBLANK($I$17:$I$451))))}

Hi djapigo,

I think it works...! I've slightly adjusted it, however, so that the not isblank extends to both columns. It seems to work (having done a manual check), but let me know if the below looks wrong.

{=SUM((Sheet1!$I$17:$I$451<Sheet1!$H$17:$H$451)*(NOT(ISBLANK(Sheet1!$I$17:$I$451))*(NOT(ISBLANK(Sheet1!$H$17:$H$451)))))}

Thanks very much for your help!

Hi Tony,

FYI, there are numbers in column H.

The reduction you've suggested works just as well as the previous suggestions.

Final result is (if my understanding is correct): {=SUM((Sheet1!$J$17:$J$451<Sheet1!$I$17:$I$451)*(Sheet1!$J$17:$J$451<>"")*(Sheet1!$I$17:$I$451<>""))}

Thanks!

Sorry - I meant to say there are NEGATIVE numbers in column H.

Nice work, Roxana... I'm glad it worked...

Nice advice, Tony...

Nice work, Roxana... I'm glad it worked...

Nice advice, Tony...