counting problem

[Lingg]

I have an excel sheet that has a column filled with Work orders and another column stating whether the work orders are closed. The Work orders are devided into 3 Departments so the work orders from department KK will start with LOL-KK-... then it will have some other code
like
<TABLE style="WIDTH: 169pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=225><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 80pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=107>LOL-KK-08-234</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=118>Clsd by "Name"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>LOL-KK-00-234</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Clsd by "Name"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>LOL-PP-n73</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Open from 5/12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>LOL-SS-778</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Open from 6/2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>LOL-SS-lpp</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Clsd by "Name"</TD></TR></TBODY></TABLE>

Assume Each code in the first column will start with LOL-"DepartmentCode" and the other column will start with "Clsd" or "Open".

What kind VBA or If Statement code could I place to count How many KK, pp, and SS Work orders are closed Vs Open. I guess the main problem is I don't know how to count just a partial phrase from a single cell like
if Cells()= "LOL-KK _"
So please Help!!!

 

[jmthompson]

If you have Excel 2007, the formula you would use would be

=COUNTIFS(A:A,"=*KK*",B:B,"*OPEN*")

There are VBA solutions for this, but I'd need to know the locations of your data and where you want the results displayed.