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

✭✭✭✭

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.

• Overachievers Alumni

This is where @cell comes into effect.

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

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• ✭✭✭✭

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?

• ✭✭✭✭✭✭
edited 01/04/24

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

• Overachievers Alumni

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") )

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!