=SUMPRODUCT(--(ISNUMBER(SEARCH("Jon",OFFSET(Sheet1!A1,0,0,COUNTA (Sheet1!$A:$A),COUNTA(Sheet1!1:1))))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"skijsh1979" wrote in message
...
I am trying to build a spread sheet than counts specified text strings from
numerous sheets. There will not be the same number of cells on each sheet
with a value. I need to be able to count certain values while excluding
the
blank cells. can anyone out there help?

Thanks,

Justin