Excel-Overcoming #VALUE error with FIND function

Find looks in a cell, not a range of cells. The syntax component within_text is looking at a particular string, but a range of cells is not a string. You may be able to achive this with an array, but the simplest approach would be to create a finder column; make a column next to the column you want to search and add the formula in each adjacent cell, so in B2 the within_text statement would be A2.

=FIND("Apple",A1)

I get the feeling you're trying to find the number of instances of the word Apple. If this is correct you could instead use this formula:

=COUNTIF(A:A,"Apple")

But this will only give you a count of all instance of the word Apple in the column A, where Apple is the entire contents of that cell.

If you want to count all instances where a cell contains Apple, even if it also contains other words (i.e Apple Pie) then you'll need to go back to your FIND statement in an extra column and use an IF statement, maybe something like this:

=IF(ISERROR(FIND("Apple",$A2)),0,1)

This will return a 0 if the FIND statement results in an error, and otherwise will return a 1. You can then simply sum that column to get the count of cells with the word Apple somewhere within them.

If you want to find the first cell (row) in Column A that contains the word “apple”, possibly as part of a larger word (e.g., “crabapple” or “applesauce”) or a sentence, use

=MATCH("*apple*", A:A, 0)