Need Formula to count if cell values are Greater than 0.5

Need Formula to count if cell values are Greater than 0.5

I have tried several different formulas to determine how many cells have a value greater than 0.5.  The cells are qtr 1, qtr2, qtr3, qtr4, qtr5, qtr6, qtr7, and qtr8.  The cells are not next to each other so it can not be a range.  Any suggesstions?

 

Forrest

Tagged:

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 01/11/19

    Hi Forrest,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • edited 01/11/19

    screen shot attached.  The 3 for # of outperforming qtrs was entered manually. This is where I need the formula

    formula help.PNG

  • Brian WBrian W
    edited 01/14/19

    You could use COUNTIFS to check for values greater than 0.5 in columns where the first row contains "QTR":

    =COUNTIFS([qtr 1]2:[qtr8]2, >0.5, [qtr 1]1:[qtr8]1, FIND("QTR", @cell) > 0)

     

    Edit: After I posted this, I realized that it should be probably be adjusted to lock the first row cells in place:

    =COUNTIFS([qtr 1]2:[qtr8]2, >0.5, $[qtr 1]$1:$[qtr8]$1, FIND("QTR", @cell) > 0)

  • Thank you, the second formula worked!  

  • I have a similar problem but instead of several columns it is several rows in one column.  I need to figure out how to determine how many cells are greater than 50% (green) out of the ? participating units.  Row 16 shows manually there are 7 above 50% out of 9 participating units noted in row 17.  I attached a screen shot.  There are rows of data that are between each that have the specific data for that unit so the formula can not be a simple range.  Can you assist?

    smartsheet formula help.PNG

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Forrest,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.