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

3 Replies

  • 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.

Resources