CountIF
I used Excel and had used a formula like this =COUNTIF(B5:R5,"Y")/COUNTA(B5:R5) to calculate the number of Y's that were in the sheet. I then converted this answer to a percentage so that I could look at my data and see the percentage of times that something was occuring. I am trying to do the same thing in Smartsheets now, but no matter how I write the formula I am getting an unparseable error. Can someone help me? I am also looking to add into this formula the ability to count out the three options available. Yes, PTA, and No.
Answers
-
Smartsheet does not have a COUNTA function. Try replacing it with a basic COUNT.
-
Example sheet:
Yes Count formula:
=COUNTIF(Options:Options, "Yes")
CountIF Contains Y formula:
=COUNTIF(Options:Options, CONTAINS("Y", @cell))
PTA Count formula:
=COUNTIF(Options:Options, "PTA")
No Count formula:
=COUNTIF(Options:Options, "No")
-
I am trying to do this in the first row of this and many other columns for QA data. I guess I wasn't clear enough. I am trying to calculate these values for Yes, PTA, and No in one formula. Do i need 3 in 3 seperate rows? I want this data on the top of the column so it can be referenced quickly when i open the sheet.
-
You're not going to be able to have a single cell provide 3 different calculations with a single formula. You have 2 options:
Option 1: Create a separate column for each calculation.
Option 2: Use a single column but have 3 separate rows that calculate different data, such as this:
-
Ok, thank you, I will work on this tomorrow and see if I can get the formula to work. I appreciate the help and information
-
You can have a single formula outputting 3 different values as long as you structure it right and don't need to actually reference them as numbers (still possible but adds a layer of complexity)
Something like this:
="Yes: " + COUNTIFS(CHILDREN(), @cell = "Yes") + CHAR(10) + "PTA: " + COUNTIFS(CHILDREN(), @cell = "PTA") + CHAR(10) + "No: " + COUNTIFS(CHILDREN(), @cell = "No")
Once you enable text wrapping your cell will look like this:
Yes: 15
PTA: 10
No: 5
-
Nice. I didn't think of doing that. Good one.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!