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?

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jafarz" wrote in message
...
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?

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" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jafarz" wrote in message
...
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?

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

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jafarz" wrote in message
...
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" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jafarz" wrote in message
...
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?