NA() and COUNTIF functions together?

[amandaTES]

Hi everyone,

Here is what I need to do:

If cells AS2:AS4 are either blank or filled with zeros, I want cell AS6 to display #N/A. I can do this using this function: =IF(SUM(AS2:AS4),0,"#N/A")

however, if cells AS2:AS4 are not blank, I want to count the cells that contain a value of 2 and display the total in cell AS6, which I can do using this function: =COUNTIF(AS2:AS4,"2")

What I don't know is how to get both functions to work simultaneously. Can anyone help?

Thanks!
Amanda

 

[amandaTES]

I really need some help because this is creating a roadblock in my productivity!

I have been racking my brain all morning and came up with this:
=IF(SUM(AL2:AL4=0),"#N/A",(COUNTIF(AL2:AL4,"2")))

It doesn't work either. I get a #VALUE error no matter what.