ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Counting entries which match specified criterial (https://www.excelbanter.com/excel-discussion-misc-queries/261563-counting-entries-match-specified-criterial.html)

Colin_site
Counting entries which match specified criterial
 
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[_2_]
Counting entries which match specified criterial
 
{=SUM((LEFT(anclist;3)="ANC")*(status2="ongoing")) }


On 15 Kwi, 11:44, 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...

Jarek Kujawa[_2_]
Counting entries which match specified criterial
 
sorry

this one is correct:

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

On 15 Kwi, 12:03, Jarek Kujawa wrote:
{=SUM((LEFT(anclist;3)="ANC")*(status2="ongoing")) }

On 15 Kwi, 11:44, 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...- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

MS-Exl-Learner
Counting entries which match specified criterial
 
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...

Colin_site[_2_]
Counting entries which match specified criterial
 
great.. both solutions work. Thank you very much. Any suggestions why the
wildcard solution i initally wrote didn't work.

thanks again.

colin


"Ms-Exl-Learner" wrote:

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

Teethless mama
Counting entries which match specified criterial
 
=SUMPRODUCT((ISNUMBER(SEARCH("ANC*",anclist)))*(st atus2="ONGOING"))

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


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



Ms-Exl-Learner" wrote:

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

MS-Exl-Learner
Counting entries which match specified criterial
 
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!!!

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


"Teethless mama" wrote:

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

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


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



Ms-Exl-Learner" wrote:

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


All times are GMT +1. The time now is 10:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com