Forum Discussion

KEV13OY's avatar
KEV13OY
Copper Contributor
Apr 19, 2021

Execel countif

Hi   I am trying to use =(LEN(AD:AD)-LEN(SUBSTITUTE(AD:AD,Sheet1!P6,"")))/LEN(Sheet1!P6) to return a count with a specific word used in the sentence. I get #SPILL! returned.   is it too large?
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 19, 2021

KEV13OY 

That's since you selected entire column AD, operation is performed on all 1 million rows in the column and that's not enough space to return result. Take more exact range like AD1:AD100 and be sure you have 100 empty cells under the cell with the formula to return result.

  • KEV13OY's avatar
    KEV13OY
    Copper Contributor
    Apr 19, 2021

    SergeiBaklan 

    HI 

     

    Thank you added AD2:AD2000 and it worked.

Resources