Excel 2007 : COUNTIF gives undesired result

[Buscador]

Hello,
The formula, =COUNTIF(H7:H31,">0")counts the number of cells with positive numbers which is fine.

The formula, =COUNTIF(H7:H31,"<0")counts the cells with negative number in them. Also fine.

However, the formula, =COUNTIF(H7:H31,"<>0") Counts all cells (25) before any data, positive or negative is introduced and does not count when new entries are introduced.

I need a count whether positive or negative entries are entered in the cells.

Please, any help would be greatly appreciated.

Buscador

[Bob Phillips]

Try

=SUMPRODUCT(--(H7:H31<>""),--(H7:H31<>0))

[Marcol]

Have you tried this ? ...

Please Login or Register  to view this content.

Or if you don't want to count zeros

Please Login or Register  to view this content.

[Haseeb Avarakkan]

If you don't want to count zeros,

=SUM(COUNTIF(H7:H31,{">0","<0"}))

[Buscador]

Thank you all, guys, for your help. Finally, I used Marcol's suggestion not to count zeros which worked perfectly.
Your help, from the three of you, was very much appreciated.

Buscador happy camper.

[Marcol]

@ Haseeb
Nice one again!
I gave up trying arrays with COUNTIF() a long time ago, I never thought of wrapping it in SUM()