Counting entries which match specified criterial

  • Thread starter Thread starter Colin_site
  • Start date Start date
C

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...
 
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.
 
great.. both solutions work. Thank you very much. Any suggestions why the
wildcard solution i initally wrote didn't work.

thanks again.

colin
 
=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"))
 
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!!!
 
Back
Top