Results and expressing as percentage
In my forms, I have several questions that can result as either PASS, FAIL or N/A. I want to express only the FAIL cells as a percentage over the entire row results. I've been using this formula:
=COUNTIF(CellName1@row:CellName2@row, CONTAINS("PASS", @cell)) / 7
The only way I've been able to make this work is by counting all of the PASS cells and dividing the result by the number of cells (7) I want to compare it to.
This has drawbacks:
-As long as all of my answers are either Pass or Fail, the percentage expresses correctly. But, when I choose N/A, the expression fails.
-I also can't express this result WITHOUT counting the PASS cells.
Ideally, I want this formula to say something such as "Count all cells in row that have the word "FAIL" in them, compare that to the total number of cells I wanted to check, and tell me the total as a percentage."
How do I accomplish this?
Best Answers
-
@jblunda try this formula
=iferror(countif(CellName1@row:CellName2@row, CONTAINS("FAIL", @cell))/ countif(CellName1@row:CellName2@row, not(CONTAINS("N/A", @cell))),"")
-
I don't get the expected result, but I found a way to make it work:
=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell))),"")
When I use FAIL, I get the reverse answer of 80% (between 5 cells). If I switch that to PASS, I get my result.
Thank you, and I had no idea I could just write another argument in the denominator!
-
Gotcha! I though you were looking to see the percentage of fails. Glad you got it working
-
You still have a syntax error which is why you are getting unparseable it should look like below
=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")
-
I think I missed a parenthesis on the other one I sent, my apologies
This should work
=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")
Answers
-
@jblunda try this formula
=iferror(countif(CellName1@row:CellName2@row, CONTAINS("FAIL", @cell))/ countif(CellName1@row:CellName2@row, not(CONTAINS("N/A", @cell))),"")
-
I don't get the expected result, but I found a way to make it work:
=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell))),"")
When I use FAIL, I get the reverse answer of 80% (between 5 cells). If I switch that to PASS, I get my result.
Thank you, and I had no idea I could just write another argument in the denominator!
-
Gotcha! I though you were looking to see the percentage of fails. Glad you got it working
-
A new question that can be added to this discussion:
Let's say I want the formula to add those cells that ONLY have either PASS or FAIL in them, and IGNORE them if they are blank or contain "N/A". How do I add a secondary condition in the denominator that address more than one criteria to NOT consider? Do I add another NOT argument?
-
@jblunda yeah you can add as many critera's as needed so something like
=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, <>""),"")
the <>"" is essentially the cell is not blank. I think you could also replace that with not(isblank(@cell)) if you wanted.
-
No luck, yet: "##INCORRECT ARGUMENT SET"
I also tried adding the not argument at the back:
=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell)), not(isblank(@cell)),"")
I also played with syntax to see if there was a simple missing () and added some spaces, but nothing panned out.
-
@jblunda the formula always has a (range, critera) format, you are missing the range for your last criteria
=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, not(isblank(@cell)),"")
cellname1@row:cellname2@row is your range, and
not(isblank(@cell)) is the critera,
and you would repeat that for each criteria.
-
I've found a workaround, mainly because I'm stuck with an error I don't understand:
#UNPARSABLE:
=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell))), (cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), ""))
The goal: count all cells (within a specific range) with the word "PASS". Divide that number by a number gained by counting all cells (within a specific range) that have either PASS, FAIL or N/A. Express answer as percentage.
Its the denominator I can't understand. So, I simplified:
=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")
This formula works for what I need. Am I missing a () somewhere?
-
You still have a syntax error which is why you are getting unparseable it should look like below
=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")
-
I think I missed a parenthesis on the other one I sent, my apologies
This should work
=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!