(SOLVED) Count words/items in cells

**lindsayc** · 03-18-2010, 05:38 PM

Hi all

I have a MASSIVE spreadsheet I need some help formulating results on. There are a total of 650 rows of information that are all similar.
I have attached a sample as the formating is not corrct when I post

The dots in the report number represent three samples on one report. I need to be able to count the total number of samples taken, how many were on pipe and how many were on foam, how many were NAD and how many were not NAD. I have tried the COUNTIF, SUMIF, COUNT and several other function, all of which return a 0 result.

If anyone could help me you would save me numerous hours of going through each report. Thanks guys and have an awesome day

**NBVC** · 03-18-2010, 05:54 PM

In H4 enter the word: NAD

in H5 enter: FOAM

In H6 enter: PIPE

Now in I4, enter:

=SUMPRODUCT(--((LEN($E$8:$F$10)-LEN(SUBSTITUTE($E$8:$F$10,H4,"")))/LEN(H4)))

and copy down

This counts the number of times those words appear in that range E8:F10

Is that what you want?

Edit:

If you want out how many times the keywords were not those cells, then in J4: =SUM($I$4:$I$6)-I4 copied down... where I4:I6 contains the keyword counts done above.

**Larry.LeBlanc@O** · 03-18-2010, 05:57 PM

I used:
=IF(ISERROR(SEARCH("NAD",F8)),0,1)

This says if you get an error when you search for NAD in F8, the value is 0 (no occurrences)
If you don't get an error, the value is 1 (NAD is contained there).
Then count the 1's to see how many cells contain NAD at least once

P.S. Column F looked like it contained "NAD". However, when I made it wider, I could see that it contained much more.

**lindsayc** · 03-18-2010, 06:22 PM

Thats pretty great!! Thanks!!!
Any idea on how I can count the total number of samples taken (Column D)
Thank so much for your help =)

**Larry.LeBlanc@O** · 03-18-2010, 06:30 PM

Is Y-520-A a single sample? If so, you need separating unique characters, such as a /, and you can count those occurrences like you counted NAD.

**Larry.LeBlanc@O** · 03-18-2010, 06:32 PM

In our original post, I don't understand:
"The dots in the report number represent three samples on one report."

C8 has 2dots and 2 report numbers.How many reports is that?

**lindsayc** · 03-18-2010, 06:40 PM

Yes Y-520-A is a single sample, with the back up data read straight across. So basically each pipe has several samples on different areas and/or materials.
So I would have to add a distinguishing mark to each sample number?
In the example Y-520-A would the - work? If it counted it twice I could just divide the number by 2?
As for the dots, the first report only has one sample on that report, the second report has four samples. Hope that helps!

**NBVC** · 03-18-2010, 10:52 PM

Originally Posted by lindsayc

So I would have to add a distinguishing mark to each sample number?
In the example Y-520-A would the - work? If it counted it twice I could just divide the number by 2?

Yes the - would work:

=SUMPRODUCT(--(LEN(D8:D10)-LEN(SUBSTITUTE(D8:D10,"-",""))))/2

This gives 13 for your sample workbook.