countif only when an exact match occurs

M

Mike

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
 
T

T. Valko

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 ")
 
E

Elkar

See if this works:

=SUMPRODUCT(--(TRIM('1208ATMDispatches'!$B$10:$B$63)="Branch"))

HTH
Elkar
 
M

Mike

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

T. Valko

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
 
M

Mike

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top