Assume that you are having the Item and Status in A1 to B100

Copy and paste the below formula other than A1 to B100 cell.
=SUMPRODUCT((ISNUMBER(SEARCH("ANC*",A1:A100)))*(B1 :B100="ONGOING"))

If you are using Name Ranges in your formula then try the below:-
=SUMPRODUCT((ISNUMBER(SEARCH("ANC*",anclist)))*(st atus2="ONGOING"))

But be sure that the named ranges must be defined with the same number of
cell ranges.
For Example anclist = A1:A100 then status2 should also have the same cell
numbers like B1:B100.

No need to use Cntrl+Shift+Enter for the above, since it is not an array
formula.

--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Colin_site" wrote:

I have the below table and I would like to count how many "ANC" entries are
ongoing. The first column range is called "anclist" with the second called
"status2".

Using the array formular below excel 2003 returns a count of too

{=SUM((anclist="anc141")*(status2="ongoing"))}

However when i use a wildcard as noted in the formular below, to expand the
search the formular fails.

{=SUM((anclist="anc*")*(status2="ongoing"))}

Item status
ANC141 ongoing
ANC142 closed
CNC001 closed
ANC144 ongoing
ANC141 ongoing
CNC010 Closed
ANC147 ongoing

Any idea's suggetions would be appreciated...