COUNTIFS and ISNUMBER Formula Help - How to count a cell only if the value is a number?

KarenTF

I have a form that is a series of questions regarding requirements. Some requirements will apply to some sites and some will be N/A for some sites depending on what each site is set up for. When a requirement does apply, there will be a scoring system associated with each requirement which will result in an adherence score at the end. The score needs to only count the requirements that apply, and not count the N/A options. How can I use the formulas COUNTIFS and ISNUMBER correctly to achieve this? Here are the columns I'm looking at and I'm trying to determine the Max Possible points by only counting the cells that have a number value (excluding the N/A) and multiplying by 3 (since that is the highest score that can be achieved):

The end idea is to sum all the values in Score Total (I know how to do that formula). And then that Score Total divided by the Max Score Possible will give the adherence score (which I know how to do as well). Remember, that each site will have varying N/A responses so I need to figure out the correct formula that will cover all scoring possibilities.

Brian_Richardson

This is where @cell comes into effect.

= COUNTIF( [TS Score Value]@row:[RR Score Value]@row , ISNUMBER(@cell) )

KarenTF

Something isn't working, when I put in the formula, it is giving me "0" as an output even though there are number values in some of those fields. Does it have any affect since formulas are being used to pull in those values?

DKazatsky2

Hi @KarenTF,

Try using one of these.

=SUMIF([TS Score Value]@row:[RR Score Value]@row, ISNUMBER(@cell))

OR

=IF(ISNUMBER([TS Score Value]@row), [TS Score Value]@row) + IF(ISNUMBER([EA Score Value]@row), [EA Score Value]@row) + IF(ISNUMBER([RR Score Value]@row), [RR Score Value]@row)

Hope that helps,

Dave

Brian_Richardson

https://community.smartsheet.com/discussion/comment/411873#Comment_411873

Is it possible that those numbers are actually text? As an alternative, instead of using ISNUMBER you could check for "N/A".

= COUNTIF( [TS Score Value]@row:[RR Score Value]@row , <>"N/A") )