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