6

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

Functionality

Comments

Andree_Stara

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

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?