| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting entries | Excel Discussion (Misc queries) | |||
| counting entries | Excel Worksheet Functions | |||
| Counting entries | Excel Worksheet Functions | |||
| Counting number of row entries | Excel Discussion (Misc queries) | |||
| Counting Entries | Excel Worksheet Functions | |||
Linear Mode
