Count formula

[ashani]

Hi experts,

I'm looking for a guidance regarding the below formula - currently it counts how many incorrects are there however what I want is : Between E30:M30 - however many incorrects selected take one off so for example if 8 columns selected as incorrect than it should show 7. However, if all of them showing as correct than it should show 0 (zero).

=IF(IF(COUNTIF(E30:M30,"N/A")=COUNTA(E30:M30)+COUNTBLANK(E30:M30),1,0)=1,"",IF(COUNTBLANK(E30:M30)>0,"",COUNTIF(E30:M30,"Incorrect")))

Many thanks,

 

[RoryA]

Sounds like you want:

MAX(0,COUNTIF(E30:M30,"Incorrect")-1)

 

[ashani]

Hi @RoryA
Thank you for the prompt response. It's working fine - the only issue is if E30:M30 is blank than it's still showing "0" - any chance it only show number when every column in that range is filled either with correct/incorrect and if all blank than leave it blank.

Thanks,

 

[RoryA]

If they are all blank then the first part of your formula should already be taking care of that.

 

[ashani]

Thank you