Searching and counting text in a spreadsheet

[jafarz]

Is there a way to search an entire multiple sheet spreadsheet for a
particular text string, count how many times it occurs and return the sum of
the total number of times the text occurs?

 

[Don Guillett]

Try this idea from a recent positng of mine where a, b,c are names of
worksheets and a2 is what you are looking for and a1:h10 is the search area

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"b","c","d","e"}&"'!a1:h10"),A2))

 

[jafarz]

Thanks Don, i couldnt get that to work. What would the formula be if i wanted
to search sheets "Sheet2" and "Sheet3" for all occurances of the text "fbl"?
If it works i can add the additional sheets to the formula.

Brian

 

[Don Guillett]

Just change
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"b","c","d","e"}&"'!a1:h10"),A2))

&{"sheet2","sheet3"),A2))
=a1:z100")
"fbl"