countif

[jswaim]

Hi, I am working with some data, and need to find certain models, that have a certain option code, and are below an MSRP of $35,000. The model code is in column A, Option codes are in column B, and MSRP, is in Column E. Below is an example. The option I need code is the last one in column B (1SJ). I have hundred of similar units at different prices. So, Here is what I need to determine, How may CC15936's do I have with the 1SJ option, that are below an MSRP of $34,000 dollars. Thanks in advance for your help.

Also, I would like you all to know, that I have many different models as well, so a simple countif will not work.

(Column A) CC15936
(Column B)AE7,AG1,B30,CJ3,C49,DL8,EN4,FE9,GT4,K34,LM7,M30,PDE,PF9,QMJ,R9U,SAF,T96,UB0,VB3,V54,V76,WBH,ZW9,ZY1,Z82,1SJ
(Column C) 34,000[/list]

 

[Aladin Akyurek]

Yes

You skipped one of the questions...

=SUMPRODUCT(($A$2:$A$3041=V9)*(ISNUMBER(MATCH("*"&W9&"*",$B$2:$B$3041,0)))*($G$2:$G$3041>=X9)*($G$2:$G$3041<=Y9)).

where X9 houses the lowest dollar value, Y9 the highest.

 

[jswaim]

Sorry I was in a hurry. The new one looks like it works great, I am going to play around with it for a while. Thank you very much for your help!

 

[jswaim]

Okay, There is a bit of a problem. I am finding cc15936's, 1sj's, in the 35000 to 36000 price range. I do not have any. I do have cc15936's, 1SN's, in that range but not 1sj's. Maybe it is getting confussed because of all the options in the cell the 1sj is located in. What do you think? I am trying to investigate the problem, I am not sure why. I just rechecked and it is also happening if I change the cc15936 to ck15936, it will tell me that I have a unit that I do not.

 

[jswaim]

Anyone have any suggestions to this problem?

 

[just_jon]

Hi, Jason -- How many characters we got in any given cell, max, in column B? (Beginning to smell a long string bug...)

 

[jswaim]

Well, Jon
There are various characters in column B. This is the option code cell, some units will have more/ and or different options than others. There are also different models in column A. Hope this is what you were talking about. The options in column B are separated by comma's if that helps.

 

[just_jon]

No, I was wondering how long the strings in column B might get. For example,

1JS,3SJ,XXX,111,YYY

would be 19 characters. I was wondering what the length of some of your longer cell inputs might be.

You can determine the maximum length in the range Bx:By by:

=MAX(LEN(Bx:By))

entered via CONTROL+SHIFT+Enter, where x is your 1st row number in B and y is the last row containing your strings.

 

[jswaim]

Jon,
There are sometimes over 200 characters in column b.

 

[just_jon]

Thought that might be the case; Aladin's on it, so you've got the best working w/ you. In case you're wondering what I was thinking, look at the 2 threads below:

http://216.92.17.166/board2/viewtopic.php?t=50630 and http://216.92.17.166/board2/viewtopic.php?t=60276

 

[jswaim]

Hey, Aladin,
Just the man I need, I am still having trouble with this formula, do you have any advice?