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]

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

Did you check your PM?

 

[jswaim]

Aladin, What is a PM? Sorry for the stupid question, but I don't know.

 

[PaddyD]

'Private Message. See the links in the top right of the page.

 

[Aladin Akyurek]

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

The MATCH bit in

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

is a mistake. While

MATCH(Range,Cond,0)

is correct,

MATCH(Cond,Range,0)

is not, given the nature of the query. The Match bit in such cases must be replaced with SEARCH(Cond,Range). Thus:

=SUMPRODUCT(($A$2:$A$3041=V9)*(ISNUMBER(SEARCH(W9,$B$2:$B$3041)))*($G$2:$G$3041>=X9)*($G$2:$G$3041<=Y9))

Jon: The lenght of the strings in B does not seem to constitute a problem here.

 

[just_jon]

is a mistake. While

MATCH(Range,Cond,0)

is correct,

MATCH(Cond,Range,0)

... ... Maybe I'm in need of a remedial reading class. Know any good instructors, Aladin?