Counting entries which match specified criterial

[Colin_site]

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...

 

[Jarek Kujawa]

{=SUM((LEFT(anclist;3)="ANC")*(status2="ongoing"))}

 

[Jarek Kujawa]

sorry

this one is correct:

{=SUM((LEFT(anclist,3)="ANC")*(status2="ongoing"))}

 

[Ms-Exl-Learner]

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)))*(status2="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.

 

[Colin_site]

great.. both solutions work. Thank you very much. Any suggestions why the
wildcard solution i initally wrote didn't work.

thanks again.

colin

 

[Teethless mama]

=SUMPRODUCT((ISNUMBER(SEARCH("ANC*",anclist)))*(status2="ONGOING"))

SEARCH function acting like a wild card, so you don't need a "*"


=SUMPRODUCT((ISNUMBER(SEARCH("ANC",anclist)))*(status2="ONGOING"))

 

[Ms-Exl-Learner]

Yes you are right! First I thought to go for Find function but when
considering the OP's post I have been diverted to use the search function
(since search function will accept wildcards and it treats the upper and
lower case letters as same) and by default I have also made the * in my
formula also. After seeing your reply I come to know my mistake.

Thanks for your guidance!!!