Forum Discussion

Adam_Speerstra's avatar
Adam_Speerstra
Copper Contributor
Mar 12, 2019

COUNTIF function

I am trying to use the following COUNTIF formula =countif($f$7:$aj$7,”(f7/f6)>.95”) I am not getting a result and can’t figure out why. If I use the evaluate function it functions properly up until ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 12, 2019

With that function you compare each cell of your range with the text in second parameter. I guess no such texts in the range, result shall be zero.

 

You may use

=SUMPRODUCT( --($F$7:$AJ$7/$F$6:$AJ$6>0.95))

if there are no blank cells in row 6.

 

  • Adam_Speerstra's avatar
    Adam_Speerstra
    Copper Contributor
    Mar 12, 2019

    What I am trying to do with the function is compare efficiencies by day. If the production line produces 95% or high of the plan I want it to count 1. Attached is a basic example of my spreadsheet. The spreadsheet is very large and adding extra rows is extremely time consuming and difficult due to the other sheets linked to this sheet. If actual production hasn't happened yet then the cell will read "" not blank also. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 12, 2019

      You may use array formula (Ctrl+Shift+Enter to enter) as

      =SUM(--IFERROR(B4:T4/B3:T3>0.95,0))

      It's in B6 in attached

       

      • Adam_Speerstra's avatar
        Adam_Speerstra
        Copper Contributor
        Mar 26, 2019

        Unfortunately this didn't work either. Row 4 contains an if formula that reads another sheet. The if formula returns " " if the cell in the other sheet is blank (if(isblank(Sheet1!A4),"",Sheet1!A4) for example. That is what is making this so difficult. SergeiBaklan 

Resources