Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count if colum begins with
How do I create a formula to count using begins with. Example, I want to
count the number of time the column begins with other than closed, cancel, Open, or column is blank. I already have the columns that start with open, closed, etc counting for the total number that are open or closed. I also need to know the number of time the column starts with other than open, closed, etc. Countblank is also counting the number of blank columns. The columns are all words and not number value, but I want the accurance to count. I was thinking there should be a way to countif columns does not begin with "open, closed or Cancel" but I can not get it to work. Hope you can help. Columns; Open Cancelled due to transfer Planning/Research Draft Report Closed |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
count if colum begins with
This gives the count of cells in A1:A10 that do not begin with "open",
"closed", or "Canceled". Includes blank cells: =SUMPRODUCT((LEFT(A1:A10,4)<"open")*(LEFT(A1:A10, 6)<"closed")*(LEFT(A1:A10,6)<"Cancel")) If you don't wan't blanks: =SUMPRODUCT((LEFT(A1:A10,4)<"open")*(LEFT(A1:A10, 6)<"closed")*(LEFT(A1:A10,6)<"Cancel")*(NOT(ISBL ANK(A1:A10)))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "linda" wrote: How do I create a formula to count using begins with. Example, I want to count the number of time the column begins with other than closed, cancel, Open, or column is blank. I already have the columns that start with open, closed, etc counting for the total number that are open or closed. I also need to know the number of time the column starts with other than open, closed, etc. Countblank is also counting the number of blank columns. The columns are all words and not number value, but I want the accurance to count. I was thinking there should be a way to countif columns does not begin with "open, closed or Cancel" but I can not get it to work. Hope you can help. Columns; Open Cancelled due to transfer Planning/Research Draft Report Closed |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
count if colum begins with
You can use wildcards with COUNTIF, so you could have:
=COUNTIF(A:A,"Open*") to count all the cells in column A which start with "Open". In a similar way, this one will count those cells which do not start with "Open": =COUNTIF(A:A,"<Open*") Hope this helps. Pete On Jan 14, 6:34*pm, linda wrote: How do I create a formula to count using begins with. *Example, I want to count the number of time the column begins with other than closed, cancel, Open, or column is blank. *I already have the columns that start with open, closed, etc counting for the total number that are open or closed. *I also need to know the number of time the column starts with other than open, closed, etc. *Countblank is also counting the number of blank columns. *The columns are all words and not number value, but I want the accurance to count. * I was thinking there should be a way to countif columns does not begin with "open, closed or Cancel" but I can not get it to work. Hope you can help. Columns; Open Cancelled due to transfer Planning/Research Draft Report Closed |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
count if colum begins with
Maybe this:
=COUNTA(A1:J1)-SUM(COUNTIF(A1:J1,{"open","closed","cancel*"})) -- Biff Microsoft Excel MVP "linda" wrote in message ... How do I create a formula to count using begins with. Example, I want to count the number of time the column begins with other than closed, cancel, Open, or column is blank. I already have the columns that start with open, closed, etc counting for the total number that are open or closed. I also need to know the number of time the column starts with other than open, closed, etc. Countblank is also counting the number of blank columns. The columns are all words and not number value, but I want the accurance to count. I was thinking there should be a way to countif columns does not begin with "open, closed or Cancel" but I can not get it to work. Hope you can help. Columns; Open Cancelled due to transfer Planning/Research Draft Report Closed |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
count if colum begins with
ok i think I am all most there, this includes the "blank" columns, I do not
want to count the blank columns, I want to count the columns that do not have "open, closed, or are blank. This will be the ones that start with other words. thanks "T. Valko" wrote: Maybe this: =COUNTA(A1:J1)-SUM(COUNTIF(A1:J1,{"open","closed","cancel*"})) -- Biff Microsoft Excel MVP "linda" wrote in message ... How do I create a formula to count using begins with. Example, I want to count the number of time the column begins with other than closed, cancel, Open, or column is blank. I already have the columns that start with open, closed, etc counting for the total number that are open or closed. I also need to know the number of time the column starts with other than open, closed, etc. Countblank is also counting the number of blank columns. The columns are all words and not number value, but I want the accurance to count. I was thinking there should be a way to countif columns does not begin with "open, closed or Cancel" but I can not get it to work. Hope you can help. Columns; Open Cancelled due to transfer Planning/Research Draft Report Closed |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
count if colum begins with
If that counts blank columns then that means you either have formulas that
return formula blanks ("") or, there is some unseen charater(s) in the cells like space characters. If you're dealing with *TEXT* entries only try this: =COUNTIF(A1:J1,"?*")-SUM(COUNTIF(A1:J1,{"open","closed","cancel*"})) That will account for formula blanks. -- Biff Microsoft Excel MVP "linda" wrote in message ... ok i think I am all most there, this includes the "blank" columns, I do not want to count the blank columns, I want to count the columns that do not have "open, closed, or are blank. This will be the ones that start with other words. thanks "T. Valko" wrote: Maybe this: =COUNTA(A1:J1)-SUM(COUNTIF(A1:J1,{"open","closed","cancel*"})) -- Biff Microsoft Excel MVP "linda" wrote in message ... How do I create a formula to count using begins with. Example, I want to count the number of time the column begins with other than closed, cancel, Open, or column is blank. I already have the columns that start with open, closed, etc counting for the total number that are open or closed. I also need to know the number of time the column starts with other than open, closed, etc. Countblank is also counting the number of blank columns. The columns are all words and not number value, but I want the accurance to count. I was thinking there should be a way to countif columns does not begin with "open, closed or Cancel" but I can not get it to work. Hope you can help. Columns; Open Cancelled due to transfer Planning/Research Draft Report Closed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
How do I count different names in a colum ie: 4 mikes 3 toms | Excel Discussion (Misc queries) | |||
How to count how many different items in one colum? | Excel Worksheet Functions |