Forum Discussion

NewelleNielsen8821's avatar
NewelleNielsen8821
Copper Contributor
Nov 10, 2021
Solved

#spill Error

Hello I have this function above a defined table      =ROW(Colorado)-1+COUNTA(Colorado[Description])   The Error states its a "Spill Range Isn't Blank" and when I select the Obstructing Cells its...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Nov 10, 2021
    so maybe the original formula you wanted was:
    =ROWS(Colorado)-COUNTA(Colorado[Description]) & " REG NEEDED"
    or could be:
    =COUNTBLANK(Colorado[Description]) & " REG NEEDED"
NewelleNielsen8821's avatar
NewelleNielsen8821
Copper Contributor
Nov 10, 2021
Thanks that is much easier - appreciate the hint
NewelleNielsen8821's avatar
NewelleNielsen8821
Copper Contributor
Nov 10, 2021
So now just need to figure how how to use the rows() function in place of my literals:

=COUNTBLANK($C$4:$C$31)& " REG NEEDED"
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 10, 2021
    so maybe the original formula you wanted was:
    =ROWS(Colorado)-COUNTA(Colorado[Description]) & " REG NEEDED"
    or could be:
    =COUNTBLANK(Colorado[Description]) & " REG NEEDED"
    • NewelleNielsen8821's avatar
      NewelleNielsen8821
      Copper Contributor
      Nov 10, 2021

      well instead of have the C2:C27 I wanted to use something like this

      =COUNTBLANK(ROWS(Colorado[Regulatory Reference])) & " REG NEEDED"

      but that isn't working I tried IS BLANK with the same and it doesn't work either. 
      I just wanted to make so I didn't have to check each tab for the number of rows and update it all manually as noted in red below

      =COUNTIF(G4:G31,"TBD") & " TBD RESOLUTION NEEDED"

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 11, 2021

        NewelleNielsen8821 

        Sorry, I didn't catch. If to count only blank cells

        =COUNTBLANK(Colorado[Regulatory Reference]) & " REG NEEDED"

         

        If only you have really blank cells, not values with empty strings returned by formulae.

         

        COUNTIF() shall work, but that's another question. Perhaps you may illustrate with sample file what exactly you'd like to calculate?

         

         

Resources