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]

 

[Todd Bardoni]

Book1
	A	B	C	D
1	MODEL	OPTION	MSRP	Result
2	CC15936	1SJ	33900
3	CC15937	2SJ	33900	2
4	CC15938	3SJ	33901
5	CC15936	1SJ	33902
6	CC15940	2SJ	33903
7	CC15941	3SJ	33904
8	CC15942	1SJ	33905
9	CC15943	2SJ	33906
10	CC15944	3SJ	33907
11	CC15945	1SJ	33908
12	CC15946	2SJ	33909
13	CC15947	3SJ	33910
14	CC15948	1SJ	33911
15	CC15949	2SJ	33912
16	CC15950	3SJ	33913
17	CC15936	1SJ	34014
18	CC15936	2SJ	34015
19	CC15937	3SJ	34016
20	CC15938	1SJ	34017
21	CC15939	2SJ	34018
22	CC15940	3SJ	34019
23	CC15941	1SJ	34020
Sheet1

Is this what you need?

 

[jswaim]

I think this may work, I will let you know tomorrow. The only thing that concerns me is that in the options column (1SJ). I have many other option codes and need to isolate the 1SJ option. For example, in the same cell I will have options ABX, NTY, EAB, and 1SJ. Anyway, I will try the code tomorrow and let you know. Thank you for your help. Jason

 

[Todd Bardoni]

Ah, I see your dilema. Well, I'm not smart enough to do it all in one formula, but, you could do:-

Book1
	A	B	C	D	E	F
1	Model	Option	MSRP	Formula1	Formula2
2	CC15936	ABX,NTY,EAB,and1SJorABC	33999	1SJ	1
3
Sheet1

With:-

=IF(ISERROR(LEFT(SUBSTITUTE(B2,LEFT(B2,FIND("1SJ",B2)-1),""),3)),"",LEFT(SUBSTITUTE(B2,LEFT(B2,FIND("1SJ",B2)-1),""),3))

=SUMPRODUCT((A2:A23="CC15936")*(D2:D23="1SJ")*(C2:C23<34000))

 

[Todd Bardoni]

Ok, instead of:-

=IF(ISERROR(LEFT(SUBSTITUTE(B3,LEFT(B3,FIND("1SJ",B3)-1),""),3)),"",LEFT(SUBSTITUTE(B3,LEFT(B3,FIND("1SJ",B3)-1),""),3))

you can use:-

=IF(ISERROR(MID(B2,FIND("1SJ",B2),3)),"",MID(B2,FIND("1SJ",B2),3))

 

[Aladin Akyurek]

Ok, instead of:-

=IF(ISERROR(LEFT(SUBSTITUTE(B3,LEFT(B3,FIND("1SJ",B3)-1),""),3)),"",LEFT(SUBSTITUTE(B3,LEFT(B3,FIND("1SJ",B3)-1),""),3))

you can use:-

=IF(ISERROR(MID(B2,FIND("1SJ",B2),3)),"",MID(B2,FIND("1SJ",B2),3))

Todd,

Using MATCH is easier...

Book4
	A	B	C	D	E	F	G	H
1	MODEL	OPTION	MSRP
2	CC15936	AE7,AG1,B30	33900		CC15936	1SJ	34000	3
3	CC15937	ISJ,GT4	33900
4	CC15938	WBH,ZW9,ZY1	33901
5	CC15936	CJ3,1SJ,UB0	33902
6	CC15940	EN4	33903
7	CC15941	K34,1SJ	33904
8	CC15942	1SJ	33905
9	CC15943	LM7,PF9	33906
10	CC15936	M30,B30	33907
11	CC15936	1SJ	37060
Sheet1

The formula in H2 is:

=SUMPRODUCT(($A$2:$A$11=E2)*(ISNUMBER(MATCH("*"&F2&"*",$B$2:$B$11,0)))*($C$2:$C$11< G2))

 

[jswaim]

Hey Thank You all for your assistance.
The formula from Aladin works like a charm. Thanks again.
Aladin Akyurek

 

[jswaim]

Okay, I find a little problem, not in the formula, but in what information I need to find. Here is the formula that works =SUMPRODUCT(($A$2:$A$3041=V9)*(ISNUMBER(MATCH("*"&W9&"*",$B$2:$B$3041,0)))*($G$2:$G$3041>=X9)). The problem I now have is determining how many units I have inbetween a price range such as 34,000 to 36,000 dollars, and suggestions as to how I can modify the formul to determine this information? Thanks again.

 

[Aladin Akyurek]

Okay, I find a little problem, not in the formula, but in what information I need to find. Here is the formula that works =SUMPRODUCT(($A$2:$A$3041=V9)*(ISNUMBER(MATCH("*"&W9&"*",$B$2:$B$3041,0)))*($G$2:$G$3041>=X9)). The problem I now have is determining how many units I have inbetween a price range such as 34,000 to 36,000 dollars, and suggestions as to how I can modify the formul to determine this information? Thanks again.

Which cells house the dollar criteria? And fow which must these criteria hold -- G2:G3041?

 

[jswaim]

Yes