how do i count specific text strings excluding blank cells?

G

Guest

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
 
B

Bob Phillips

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

Guest

I am trying to use this formula, but it says that the zero after the BNSF
3167: is incorrect. I am going to have like 30 sheets, with anywhere from 2
to 10 cells that need to be searched and counted for the text string.

=SUMPRODUCT(--(ISNUMBER(SEARCH("HV HIGH VOLTAGE",OFFSET('BNSF 2176:BNSF
3167'!,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!1:1))))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT formula I think w/ wild card & number vs number as text 8
count nonblank cells 3
How do I use countif to count values excluding blank cells 4
COUNT rows on external sheet if between date range 3
Using COUNTA and excluding formulas that gives zero or blank cells 3
Count Cells with Text longer than 4
counting colored data 9
Count total values excluding some data 6

Top