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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!