Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Problem (or suggestion for alternative)
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. -- BB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Problem (or suggestion for alternative)
Excel has an EXACT function - look in Help to see how to use it.
Hope this helps. Pete On Oct 10, 6:37*pm, FloridaHockeyGuy wrote: 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. -- BB |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Problem (or suggestion for alternative)
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" wrote in message ... 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. -- BB |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Problem (or suggestion for alternative)
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 "Pete_UK" wrote: Excel has an EXACT function - look in Help to see how to use it. Hope this helps. Pete On Oct 10, 6:37 pm, FloridaHockeyGuy wrote: 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. -- BB |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Problem (or suggestion for alternative)
I posted an example already showing how to use SUMPRODUCT and EXACT
=SUMPRODUCT(--(EXACT('[Products.xlsm]Sheet1'!$A:$A,A2))) -- Regards, Peo Sjoblom "FloridaHockeyGuy" wrote in message ... 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 "Pete_UK" wrote: Excel has an EXACT function - look in Help to see how to use it. Hope this helps. Pete On Oct 10, 6:37 pm, FloridaHockeyGuy wrote: 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. -- BB |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Problem (or suggestion for alternative)
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 "Peo Sjoblom" wrote: 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" wrote in message ... 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. -- BB |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Problem (or suggestion for alternative)
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 "FloridaHockeyGuy" wrote: 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 "Peo Sjoblom" wrote: 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" wrote in message ... 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. -- BB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif problem | Excel Worksheet Functions | |||
Problem with COUNTIF ... | Excel Worksheet Functions | |||
COUNTIF problem with NOW() | Excel Worksheet Functions | |||
countif problem | Excel Worksheet Functions | |||
countif problem | Excel Discussion (Misc queries) |