Count how many times the code is between a price

[umirin]

Hi
I'm looking for a formula that will go in F2:H4 that will count how many times the code falls between the price

the Formula results should look like this.

Code	Price 1-100	Price 100-200	Price 200+
DDL	3		1
GHG	1	3
LKJ			4

Book1
	A	B	C	D	E	F	G	H
1	Code	Price			Code	Price 1-100	Price 100-200	Price 200+
2	DDL	45			DDL
3	DDL	67			GHG
4	DDL	43			LKJ
5	DDL	6544
6	GHG	109
7	GHG	198
8	GHG	156
9	GHG	7
10	LKJ	678
11	LKJ	654
12	LKJ	333
13	LKJ	1111
Sheet1

 

[aRandomHelper]

Maybe this:

Book1.xlsm
	E	F	G	H
1	Code	Price 1-100	Price 100-200	Price 200+
2	DDL	3	0	1
3	GHG	1	3	0
4	LKJ	0	0	4
Sheet7

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIFS($A:$A,$E2,$B:$B,">=1",$B:$B,"<=100")
G2:G4	G2	=COUNTIFS($A:$A,$E2,$B:$B,">=100",$B:$B,"<=200")
H2:H4	H2	=COUNTIFS($A:$A,$E2,$B:$B,">200")

 

[hrayani]

In F2

=COUNTIFS($B$2:$B$13,">=1",$B$2:$B$13,"<=100",$A$2:$A$13,$E2)

In G2

=COUNTIFS($B$2:$B$13,">=100",$B$2:$B$13,"<=200",$A$2:$A$13,$E2)

In H2

=COUNTIFS($B$2:$B$13,">200",$A$2:$A$13,$E2)

 

[maras]

Zeszyt1
	A	B	C	D	E	F	G	H
1	Code	Price			Code	Price 1-100	Price 101 -200	Price > 200
2	DDL	45			DDL	3	0	1
3	DDL	67			GHG	1	3	0
4	DDL	43			LKJ	0	0	4
5	DDL	6544
6	GHG	109
7	GHG	198
8	GHG	156
9	GHG	7
10	LKJ	678
11	LKJ	654
12	LKJ	333
13	LKJ	1111
14
15
16
Arkusz1

Cell Formulas
Range	Formula
E2:E9	E2	=IFERROR(INDEX(code_,MATCH(0,COUNTIF($E$1:E1,IF(code_="","",code_)),0)),"")
F2:F9	F2	=IF(E2="","",COUNTIFS(code_,$E2,price,"<="&F$1))
G2:G9	G2	=IF($E2="","",COUNTIFS(code_,$E2,price,">"&F$1,price,"<="&G$1))
H2:H9	H2	=IF($E2="","",COUNTIFS(code_,$E2,price,">" & H$1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
code_	=Arkusz1!$A$2:$A$13	E2:H9
price	=Arkusz1!$B$2:$B$13	F2:H9

 

[Tom.Jones]

@umirin,

For price 100 where you need to put counting? Maybe...

Code

Price 1-100

Price 101-200

Price 200+

F2=COUNTIFS($B$2:$B$50,"<=100",$A$2:$A$50,$E2)
G2=COUNTIFS($B$2:$B$50,">100",$B$2:$B$50,"<=200",$A$2:$A$50,$E2)
H2=COUNTIFS($A:$A,$E2,$B:$B,">200")