Execel countif

New Contributor



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?

3 Replies


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.

@Sergei Baklan 



Thank you added AD2:AD2000 and it worked.

@KEV13OY , you are welcome