Can I get a count of cells that contain PLS 2

[Bill4tektips]

In Excel 2003, is there any way to get a count of cells that contain the letters PLC (cell contains PLC 1234 for instance). I would like to do it to obtain metrics from a spreadsheet containing document numbers.

 

[combo]

For multicolumn range you can use array formula(CTRL+SHIFT entered):
=SUM(--(LEN(A14)<>LEN(SUBSTITUTE(A14,"PLC",""))))
(displayed as {=SUM(...)}).


combo

 

[GlennUK]

You could use this formula:
=COUNTIF(A14,"*PLC*")

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bill4tektips]

Thanks for that Combo. What I am actually trying to do is check 3 columns in the spreadseet to see is there is a match. I have used the SUMPRODUCT function with the formula =SUMPRODUCT(('Raw Data'!$C$2:$C$5003="PLC*")*('Raw Data'!$G$2:$G$5003="Procdoc")*('Raw Data'!$I$2:$I$5003="S9"))
Can you see any reason this will not work?

 

[GlennUK]

You can't use a wildcard with a direct comparison, so replace this
'Raw Data'!$C$2:$C$5003="PLC*"
with
LEFT('Raw Data'!$C$2:$C$5003,3)="PLC"

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bill4tektips]

Thanks for that Glen, It works a treat

 

[GlennUK]

My pleasure!

Cheers, Glenn.

Beauty is in the eye of the beerholder.