How to modify formula in cell T2?

[Eloise T]

● Cell T2 needs to ignore the cells with $0 and read "11" for the number of cells WITH a number greater than zero.

Cell T2
=IF(COUNTA(C2.P2)<>0,COUNTA(C2.P2),"-")

Please see attached Excel file.

 

[Ramesh]

I shall use the CountIF function to count the values greater than zero..

=COUNTIF(C2.P2,">0")

 

[Eloise T]

This works: =IF(COUNTIF(C2.P2,">0"),COUNTIF(C2.P2,">0"),"-")

Thanks.

This works, too: =IF(SUM(C2.P2)>0,COUNTIF(C2.P2,">0"),"-")

 

[Hui]

Why not simply: =COUNTIF(C2 : P2,">0")
and apply a custom number format of
0;-0;"-"

 

[Hui]

I inserted a space between the : and the P in the Range
so that you get =COUNTIF(C2 : P2,">0")
not: =COUNTIF(C22,">0")

 

[Khalid NGO]

This works: =IF(COUNTIF(C2: P2,">0"),COUNTIF(C2: P2,">0"),"-")

Thanks.

Hi,

Your logical test and TRUE result are same, so you don't really need the IF here.
Go with just COUNTIF as mentioned above with a custom format.

Here is one more alternate:

=SUMPRODUCT(SIGN(C2:P2))

Regards,

 

[Eloise T]

Hi,

Your logical test and TRUE result are same, so you don't really need the IF here.
Go with just COUNTIF as mentioned above with a custom format.

Here is one more alternate:

=SUMPRODUCT(SIGN(C2:P2))

Regards,

Great suggestion!

 

[Eloise T]

Why not simply: =COUNTIF(C2 : P2,">0")
and apply a custom number format of
0;-0;"-"

I never considered a custom number format. Great idea!