COUNTIF formula

[afountas]

I need a countif formula that will subtract 1 if the total is greater than 0.

Thanks

 

[VoG]

Do you mean like this?

=COUNTIF(A1:A10,1)-IF(COUNTIF(A1:A10,1)>0,1,0)

 

[Seti]

If the total is greater than 0, subtracting 1 will result in a value no less than 0, so maybe: (unless I misunderstand the question)

=MAX(COUNTIF(A1:A10,condition)-1,0)

 

[afountas]

Still having trouble with this formula.

Needs to do the following

1. Count the # of times "spefic text instance" occurs in a column

2. Subtract the total of another cell if it is greater than or equal to 1.

So...

I have a seperate data set named 'yesno'

Yes
Yes
No
Yes

Want an equation that will COUNT the number of times yes occurs,
then subtract the value of another cell

ie

if in A3 I have COUNTIF(yesno,"yes")

my total will be 3. and I want to subtract cell A2 which has 4, but I do not want a negative value.

 

[braindiesel]

How about
=if(COUNTIF(yesno,"yes")>A4,0,COUNTIF(yesno,"yes")-A4)

I realize you need quotes areound the "yesno", I am just going with your model.

 

[Jonmo1]

Try

=MAX(0, COUNTIF(yesno,"yes")-A4)