Countif Problem (or suggestion for alternative)

[FloridaHockeyGuy]

I am using Countif to see if a value (in this case, the value in cell A2) is
present in a column (A) of data;
=COUNTIF('[Products.xlsm]Sheet1'!$A:$A,A2)

but the problem is that the column is full of numbers, or combinations of
numbers and text, as they are item numbers. The column is formatted as text
due to this, as well as some item numbers has a preceding 0. For example,
there will item 123 and also an item 0123.

I am using this Countif in a column in one spreadsheet to look up the values
in another, and depending on whether the answer is 0 or 1, my next column is
a VLookup function that gets the value from the next column (if the Countif
<>0), or else if puts an alternate value.

Here is the problem. If A2 = 123, and if the column does NOT have 123, but
it DOES have 0123, the result is returned as 1. Of course, then my lookup
returns an N/A since it looks for 123 and it is not in the list.

Is there any way I can do use Countif to only find EXACT matches, or would
somebody have a suggestion on how to get around this.

I thank you greatly in advance for your responses.

 

[Pete_UK]

Excel has an EXACT function - look in Help to see how to use it.

Hope this helps.

Pete

 

[Peo Sjoblom]

It's certainly possible



=SUMPRODUCT(--(EXACT('[Products.xlsm]Sheet1'!$A:$A,A2)))


works for me assuming the values in A are text



for previous Excel versions it would be


=SUMPRODUCT(--(EXACT('[Products.xls]Sheet1'!$A$1:$A$10000,A2)))


since they cannot handle the whole column using array formulas

--


Regards,


Peo Sjoblom

 

[FloridaHockeyGuy]

Thanks, but I guess I don't know how to apply EXACT to a range. I thought
EXACT was used to compare two cells. Obviously, I have the cell in my first
file, which is, for example, the value in A2 (which is an item number I would
entering in).

But I am not sure how I would know exactly which row in Column A of the
second file (which is a list of item numbers of approx. 15,000 rows) I would
compare it to.

Or would I have to insert a column first and do a MATCH to get the row
number, and use that result in the EXACT function? Or is there another
method?

I appreciate the input!
--
BB

Excel has an EXACT function - look in Help to see how to use it.

Hope this helps.

Pete

I am using Countif to see if a value (in this case, the value in cell A2) is
present in a column (A) of data;
=COUNTIF('[Products.xlsm]Sheet1'!$A:$A,A2)

but the problem is that the column is full of numbers, or combinations of
numbers and text, as they are item numbers. The column is formatted as text
due to this, as well as some item numbers has a preceding 0. For example,
there will item 123 and also an item 0123.

I am using this Countif in a column in one spreadsheet to look up the values
in another, and depending on whether the answer is 0 or 1, my next column is
a VLookup function that gets the value from the next column (if the Countif
<>0), or else if puts an alternate value.

Here is the problem. If A2 = 123, and if the column does NOT have 123, but
it DOES have 0123, the result is returned as 1. Of course, then my lookup
returns an N/A since it looks for 123 and it is not in the list.

Is there any way I can do use Countif to only find EXACT matches, or would
somebody have a suggestion on how to get around this.

I thank you greatly in advance for your responses.

 

[Peo Sjoblom]

I posted an example already showing how to use SUMPRODUCT and EXACT


=SUMPRODUCT(--(EXACT('[Products.xlsm]Sheet1'!$A:$A,A2)))


--


Regards,


Peo Sjoblom

Thanks, but I guess I don't know how to apply EXACT to a range. I thought
EXACT was used to compare two cells. Obviously, I have the cell in my
first
file, which is, for example, the value in A2 (which is an item number I
would
entering in).

But I am not sure how I would know exactly which row in Column A of the
second file (which is a list of item numbers of approx. 15,000 rows) I
would
compare it to.

Or would I have to insert a column first and do a MATCH to get the row
number, and use that result in the EXACT function? Or is there another
method?

I appreciate the input!
--
BB

Excel has an EXACT function - look in Help to see how to use it.

Hope this helps.

Pete

I am using Countif to see if a value (in this case, the value in cell
A2) is
present in a column (A) of data;
=COUNTIF('[Products.xlsm]Sheet1'!$A:$A,A2)

but the problem is that the column is full of numbers, or combinations
of
numbers and text, as they are item numbers. The column is formatted as
text
due to this, as well as some item numbers has a preceding 0. For
example,
there will item 123 and also an item 0123.

I am using this Countif in a column in one spreadsheet to look up the
values
in another, and depending on whether the answer is 0 or 1, my next
column is
a VLookup function that gets the value from the next column (if the
Countif
<>0), or else if puts an alternate value.

Here is the problem. If A2 = 123, and if the column does NOT have 123,
but
it DOES have 0123, the result is returned as 1. Of course, then my
lookup
returns an N/A since it looks for 123 and it is not in the list.

Is there any way I can do use Countif to only find EXACT matches, or
would
somebody have a suggestion on how to get around this.

I thank you greatly in advance for your responses.

 

[FloridaHockeyGuy]

Thanks Peo, with a little tweaking (have to nest it within an IF statement) I
think that just might work! Thanks a bunch, I think you have just saved me
from swallowing a bunch of Advil!!!
--
BB

It's certainly possible



=SUMPRODUCT(--(EXACT('[Products.xlsm]Sheet1'!$A:$A,A2)))


works for me assuming the values in A are text



for previous Excel versions it would be


=SUMPRODUCT(--(EXACT('[Products.xls]Sheet1'!$A$1:$A$10000,A2)))


since they cannot handle the whole column using array formulas

--


Regards,


Peo Sjoblom

I am using Countif to see if a value (in this case, the value in cell A2)
is
present in a column (A) of data;
=COUNTIF('[Products.xlsm]Sheet1'!$A:$A,A2)

but the problem is that the column is full of numbers, or combinations of
numbers and text, as they are item numbers. The column is formatted as
text
due to this, as well as some item numbers has a preceding 0. For example,
there will item 123 and also an item 0123.

I am using this Countif in a column in one spreadsheet to look up the
values
in another, and depending on whether the answer is 0 or 1, my next column
is
a VLookup function that gets the value from the next column (if the
Countif
<>0), or else if puts an alternate value.

Here is the problem. If A2 = 123, and if the column does NOT have 123,
but
it DOES have 0123, the result is returned as 1. Of course, then my lookup
returns an N/A since it looks for 123 and it is not in the list.

Is there any way I can do use Countif to only find EXACT matches, or would
somebody have a suggestion on how to get around this.

I thank you greatly in advance for your responses.

 

[FloridaHockeyGuy]

Just as a follow-up, this worked like a charm. BUT....
due to the number of cells referenced in the other spreadsheet, along with
all of the other VLOOKUPS, it took a LONG time to save, or when working on
several hundred rows of input data to process.

JUST FYI, I replaced the references to columns by using Defined Range Names,
and in one test, it cup processing time from 1 min 20 sec to 4 seconds!
Literally 25 times faster, so I learned a valuable lesson that when you have
a lot of rows referencing a large range, using Defined Range Names can save
time in a dramatic fashion.

I mention it for those, like me, who had not used them often, to highlight
how much they can improve speed and performance

Thanks again for the input, it was greatly appreciated.
--
BB

Thanks Peo, with a little tweaking (have to nest it within an IF statement) I
think that just might work! Thanks a bunch, I think you have just saved me
from swallowing a bunch of Advil!!!
--
BB

It's certainly possible



=SUMPRODUCT(--(EXACT('[Products.xlsm]Sheet1'!$A:$A,A2)))


works for me assuming the values in A are text



for previous Excel versions it would be


=SUMPRODUCT(--(EXACT('[Products.xls]Sheet1'!$A$1:$A$10000,A2)))


since they cannot handle the whole column using array formulas

--


Regards,


Peo Sjoblom

I am using Countif to see if a value (in this case, the value in cell A2)
is
present in a column (A) of data;
=COUNTIF('[Products.xlsm]Sheet1'!$A:$A,A2)

but the problem is that the column is full of numbers, or combinations of
numbers and text, as they are item numbers. The column is formatted as
text
due to this, as well as some item numbers has a preceding 0. For example,
there will item 123 and also an item 0123.

I am using this Countif in a column in one spreadsheet to look up the
values
in another, and depending on whether the answer is 0 or 1, my next column
is
a VLookup function that gets the value from the next column (if the
Countif
<>0), or else if puts an alternate value.

Here is the problem. If A2 = 123, and if the column does NOT have 123,
but
it DOES have 0123, the result is returned as 1. Of course, then my lookup
returns an N/A since it looks for 123 and it is not in the list.

Is there any way I can do use Countif to only find EXACT matches, or would
somebody have a suggestion on how to get around this.

I thank you greatly in advance for your responses.