| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif only when an exact match occurs
I have a column of data that contains text values such as "Branch" and
"Branch Remote". I need to count the occurrances for each but my Branch count is including Branch Remote. The countif statements are =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch*" ) =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch Remote*") I am using the * because the source data has a space after Branch and also after Branch Remote. How can I do an exact match using the countif statement. Any help would be appreciated. Thanks, Mike |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif only when an exact match occurs
Instead of using a wildcard why not just include the space?
=COUNTIF('1208ATMDispatches' !$B$10:$B$63,"Branch ") =COUNTIF('1208ATMDispatches' !$B$10:$B$63,"Branch Remote ") -- Biff Microsoft Excel MVP "Mike" wrote in message ... I have a column of data that contains text values such as "Branch" and "Branch Remote". I need to count the occurrances for each but my Branch count is including Branch Remote. The countif statements are =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch*" ) =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch Remote*") I am using the * because the source data has a space after Branch and also after Branch Remote. How can I do an exact match using the countif statement. Any help would be appreciated. Thanks, Mike |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif only when an exact match occurs
See if this works:
=SUMPRODUCT(--(TRIM('1208ATMDispatches'!$B$10:$B$63)="Branch")) HTH Elkar "Mike" wrote: I have a column of data that contains text values such as "Branch" and "Branch Remote". I need to count the occurrances for each but my Branch count is including Branch Remote. The countif statements are =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch*" ) =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch Remote*") I am using the * because the source data has a space after Branch and also after Branch Remote. How can I do an exact match using the countif statement. Any help would be appreciated. Thanks, Mike |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif only when an exact match occurs
Thanks T. Valko and Elkar.
As for replacing the * with a space, I tried that previously and it did not work. The Trim approach works great with test data that I enter but not with the data I pull from a website and then download it into Excel. There seems to be something when I download the data it is putting a Null character as opposed to a space. The reason I say that is because even when I use Trim by itself I still get incorrect results. I believe both ways you two provided will work but looks like I need to look at the source data I am downloading. I believe that is causing the errors. Let me look some more and I will let you know. Thanks to both of you for your help..... -- Mike "Elkar" wrote: See if this works: =SUMPRODUCT(--(TRIM('1208ATMDispatches'!$B$10:$B$63)="Branch")) HTH Elkar "Mike" wrote: I have a column of data that contains text values such as "Branch" and "Branch Remote". I need to count the occurrances for each but my Branch count is including Branch Remote. The countif statements are =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch*" ) =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch Remote*") I am using the * because the source data has a space after Branch and also after Branch Remote. How can I do an exact match using the countif statement. Any help would be appreciated. Thanks, Mike |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif only when an exact match occurs
data I pull from a website and then download it into Excel.
Ok, those are probably html non breaking spaces. They are not standard char 32 spaces. TRIM and CLEAN won't work on them. I do a lot of copy/pasting from the web and I use a macro to clean all that html junk from the data. See this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Mike" wrote in message ... Thanks T. Valko and Elkar. As for replacing the * with a space, I tried that previously and it did not work. The Trim approach works great with test data that I enter but not with the data I pull from a website and then download it into Excel. There seems to be something when I download the data it is putting a Null character as opposed to a space. The reason I say that is because even when I use Trim by itself I still get incorrect results. I believe both ways you two provided will work but looks like I need to look at the source data I am downloading. I believe that is causing the errors. Let me look some more and I will let you know. Thanks to both of you for your help..... -- Mike "Elkar" wrote: See if this works: =SUMPRODUCT(--(TRIM('1208ATMDispatches'!$B$10:$B$63)="Branch")) HTH Elkar "Mike" wrote: I have a column of data that contains text values such as "Branch" and "Branch Remote". I need to count the occurrances for each but my Branch count is including Branch Remote. The countif statements are =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch*" ) =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch Remote*") I am using the * because the source data has a space after Branch and also after Branch Remote. How can I do an exact match using the countif statement. Any help would be appreciated. Thanks, Mike |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif only when an exact match occurs
You are right T. Valko.
So I tried =TRIM(SUBSTITUTE(B1,CHAR(160),CHAR(32))) and it worked great. I will also look at the site you provided. Thanks, -- Mike "T. Valko" wrote: data I pull from a website and then download it into Excel. Ok, those are probably html non breaking spaces. They are not standard char 32 spaces. TRIM and CLEAN won't work on them. I do a lot of copy/pasting from the web and I use a macro to clean all that html junk from the data. See this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Mike" wrote in message ... Thanks T. Valko and Elkar. As for replacing the * with a space, I tried that previously and it did not work. The Trim approach works great with test data that I enter but not with the data I pull from a website and then download it into Excel. There seems to be something when I download the data it is putting a Null character as opposed to a space. The reason I say that is because even when I use Trim by itself I still get incorrect results. I believe both ways you two provided will work but looks like I need to look at the source data I am downloading. I believe that is causing the errors. Let me look some more and I will let you know. Thanks to both of you for your help..... -- Mike "Elkar" wrote: See if this works: =SUMPRODUCT(--(TRIM('1208ATMDispatches'!$B$10:$B$63)="Branch")) HTH Elkar "Mike" wrote: I have a column of data that contains text values such as "Branch" and "Branch Remote". I need to count the occurrances for each but my Branch count is including Branch Remote. The countif statements are =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch*" ) =COUNTIF('1208ATMDispatches'!$B$10:$B$63,"Branch Remote*") I am using the * because the source data has a space after Branch and also after Branch Remote. How can I do an exact match using the countif statement. Any help would be appreciated. Thanks, Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find an exact match and go to that match | Excel Discussion (Misc queries) | |||
| Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
| CountIF columnH2-H101 the number of time between ages 20-29 occurs | Excel Worksheet Functions | |||
| Using COUNTIF to find how many times 2004 occurs | Excel Worksheet Functions | |||
| formula to extract specific data if match occurs | Excel Worksheet Functions | |||
Hybrid Mode
