Exclude "0" from COUNT function

[e.cichlid]

Hi All,

I want to COUNT the occurance that any number appears in a cell (I do not want to SUM them). How can I exclude "0" from being counted? I'd rather not leave the cell blank.

Thank you.

 

[thomasbeard]

Assuming the range of values is in C2:C5
Try:

=COUNTIF(C2:C5,"<>0")

 

[e.cichlid]

Funny.
I'm using the following formula: =COUNTIF(B57;B66;B75;B84,"<> 0")
Excel stops and highlights B75. B57=0, B66=1, B75=1 and B84=1. Why does it not like B75? I get the error that states there is something wrong with my formula and I have the option to click OK of Help.

Thanks

 

[Jonmo1]

Countif only takes 2 arguments, A range and Criteria. You've supplied 5 arguments, 4 ranges and a criteria...

Are you trying to count every 9 rows (starting at 57) if NOT 0 ?

 

[thomasbeard]

If you want Excel to review the contents of cells B57, B66, B75 and B84 use:

=COUNTIF(B57,B66,B75,B84"<> 0")

If you want it to review the two ranges B57-B66 and B75-B84 use:

=COUNTIF(B57:B66,B75:B84"<> 0")

hope this helps

 

[e.cichlid]

Hmmm, so maybe COUNT is not the formula of choice.
There are 4 cells that I want to check, every 9 cells. Inbetween that range are values that do not relate to the 4 cells in question. Unless I rearrange my data. Something I want to avoid due to the vast amoutn od data that there is.

Thanks for your input.

 

[EugeneCarter]

Try =SUM(B57<>0,B66<>0,B75<>0,B84<>0)

 

[Jonmo1]

Try this

=SUMPRODUCT(--(MOD(ROW(A57:A84)+6,9)=0),--(B57:B84<>0))

Counts how many are NOT 0, but only Every 9 Rows starting From row 57.

 

[Aladin Akyurek]

Looks like...

=SUMPRODUCT(--(MOD(ROW(B57:B94)-ROW(B57),9)=0),--ISNUMBER(B57:B94),--(B57:B94<>0))

Hi All,

I want to COUNT the occurance that any number appears in a cell (I do not want to SUM them). How can I exclude "0" from being counted? I'd rather not leave the cell blank.

Thank you.

 

[enejorg]

Any ideas why this won't work? =COUNTIF(G132:G6503, "<>0")

I am using excel version 14.0.6129.5000 (32bit)