Formula to return fraction that represents the number of cells that meet certain criteria
I'd like to return a fraction such as 3/4 to show the number of cells that meet certain criteria. If it returned 3/4 that would mean 3 out of 4 cells met the criteria.
Currently, I'm using a percentage rating, but in my opinion, if you don't have at least 10 cells to evaluate, the percentage rating value isn't true in the scenario I'm using it in.
Answers
-
Try one based on this:
The COUNTIF statements count each cell that equals A, the JOIN converts the value to text, the second JOIN concatenates a "/3" at the end (3 being the number of cells you're counting.)
This makes it easier to see the targets of the cell references:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@JReisman27 thanks for the response. Shortly after I submitted my question I figured out a solution. Here's the formula I used:
=COUNTIF([Toolbox Talks are completed & available for review]@row:[All Superintendents are OSHA 30 certified]@row, "Yes") + "/" + COUNT([Toolbox Talks are completed & available for review]@row:[All Superintendents are OSHA 30 certified]@row)
-
@Shelby Cool! That solution seems a bit more elegant than mine!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am working in a separate sheet for calculating metrics. I want to count the number of items in a referenced date range in another sheet that meet certain year criteria. For example, I want to find t…
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…
- I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1…