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

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

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

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

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

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