Find Percentage Formula for Smartsheet Summary
I have two summary lines that have formulas in them and the results in both are numbers. I want to find the percentage (first number divided by second number).
The first number shows how many "tasks" are complete while the second number shows how many "total tasks" exist.
I've not had any luck with any formula combinations so I'm reaching out to the community for assistance.
Summary Line 1 is for Content QA Complete: =COUNTIF([Content QA Complete]:[Content QA Complete], 1) + ""
Summary Line 2 is for Total Number of Content Items in Course: =COUNTIF([Total Number of H5Ps]:[Total Number of H5Ps], 1) + ""
My percentage line is giving me INVALID OPERATION and reads: =([Content QA Complete]# / [Total Number of H5Ps in All Courses]#). I've tried other formulas resulting in UNPARSABLE and other errors.
Best Answer
-
Sorry for the delay.
So the +"" at the end of your formula was creating the issue.
Removing them from your formula leaves
=COUNTIF([Content QA Complete]:[Content QA Complete], 1)
And
=COUNTIF([Total Number of H5Ps]:[Total Number of H5Ps], 1)
That allows the
=[Content QA Complete]# / [Total Number of H5Ps in All Courses]#
To work.
Answers
-
If I'm understanding correctly, You have two cells that you are using in this formula correct?
Your formula might work just fine then. Just replace the # with the cell number. Or is that what you already are doing?
=([Content QA Complete]1 / [Total Number of H5Ps in All Courses]1)
or
If the % cell is on the same row as the Summary lines
=([Content QA Complete]@row / [Total Number of H5Ps in All Courses]@row)
-
Dan,
Thanks for working with me. I am already using the # with the summary row.
=([Content QA Complete]#) / ([Total Number of H5Ps in All Courses]#)
I've tried with parentheses and without parentheses. No luck!
I have the row set to provide the result in the form of a percentage.
Judy
-
Here is something I whipped up. Is this close to what you are trying to accomplish?
If you could provide a screen shot (with any private information covered) of your sheet, we might be able to come closer to you goal.
-
Ahh I see you are using an actual Page Summary. I was mistaken.
-
The column "Content QA Complete" is counting the number of boxes that are checked. In the Sheet Summary, it is using the formula =COUNTIF([Content QA Complete]:[Content QA Complete], 1) + ""
The column "Total Number of H5Ps" is also counting the number of boxes that are checked. In the Sheet Summary, it is using the formula =COUNTIF([Total Number of H5Ps]:[Total Number of H5Ps], 1) + ""
I'm trying to take the results of the Sheet Summary for these two items and get the percentage.
Make sense?
-
Sorry for the delay.
So the +"" at the end of your formula was creating the issue.
Removing them from your formula leaves
=COUNTIF([Content QA Complete]:[Content QA Complete], 1)
And
=COUNTIF([Total Number of H5Ps]:[Total Number of H5Ps], 1)
That allows the
=[Content QA Complete]# / [Total Number of H5Ps in All Courses]#
To work.
-
Thank you, Dan!
That worked!!!
Judy
-
Excellent! Glad I could help!
