CountIF

jdierking

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.

Paul Newcome

Smartsheet does not have a COUNTA function. Try replacing it with a basic COUNT.

Mike TV

@jdierking

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")

jdierking

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.

Mike TV

@jdierking

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:

jdierking

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

Paul Newcome

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

Mike TV

@Paul Newcome

Nice. I didn't think of doing that. Good one.