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.
Answers
-
This is where @cell comes into effect.
= COUNTIF( [TS Score Value]@row:[RR Score Value]@row , ISNUMBER(@cell) )
-
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?
-
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
-
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") )
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!