Finding Price Points

[JJ in SD]

Good Afternoon,

In the example below I am able to select the Non Saturn & Saturn Trucks but now I need to see where they fall within certain price points. For example (manually inserted): 2 cars fall below $10,000 ($9999 or less), 1 car falls between $10, 000 and $10,999 and two fall between $14, 000 and $14, 999, and at times some will fall above $15, 000. Is there a way to add something to this:
=SUMPRODUCT(--($A$8:$A$15=A2),--($B$8:$B$15<>""),--ISNA(MATCH($B$8:$B$15,{"Saturn","Saturn Truck"},0))) to find out where they fall?

Thanks in advance,
JJ in SD

AFOCUS-Used_October_03.XLS
	A	B	C	D	E	F	G	H	I	J
1				<$10K	$10-$11K	$11-$12K	$12-$13K	$13K-$14K	$14-$15K	>$15K
2	2004	0
3	2003	2							2
4	2002	0
5	2001	3		2	1
6	2000	1		1
7		6
8	2001	Saturn	9596
9	2000	Oldsmobile	7665
10	2003	Buick	14950
11	2001	Chevrolet	9260
12	2001	DodgeTruck	10030
13	2003	Ford	14150
14	2001	Volkswagen	9530
15	2002	SaturnTruck	14605
Sheet1

 

[tactps]

In D5, try:

=SUMPRODUCT(--($A$8:$A$15=$A5),--($B$8:$B$15<>""),--ISNA(MATCH($B$8:$B$15,{"Saturn","Saturn Truck"},0)),--($C$8:$C$15<10000))

and then copy formula down.

Then adjust the other formulas to take up the correct range.

eg in cell i3:
=SUMPRODUCT(--($A$8:$A$15=$A3),--($B$8:$B$15<>""),--ISNA(MATCH($B$8:$B$15,{"Saturn","Saturn Truck"},0)),--($C$8:$C$15<15000),--($C$8:$C$15>14000))

You can replace the "<10000" with a formula if you change the headings in row 1.

 

[Yogi Anand]

Hi JJ:

In the following illustration, I have used cell A1 to house the MAKE (or partial string as in case of Saturn for both Saturn, and Saturn Truck) of the Car -- if I leave A1 blank, it will compute for all the Makes in the database A8:C15 ...

y040222h1a.xls
	A	B	C	D	E	F	G	H	I
1			0~10	10~11	11~12	12~13	13~14	14~15	15~9.9E+304
2		2004	0	0	0	0	0	0	0
3		2003	0	0	0	0	0	2	0
4		2002	0	0	0	0	0	1	0
5		2001	3	1	0	0	0	0	0
6		2000	1	0	0	0	0	0	0
7
8	2001	Saturn	9596
9	2000	Oldsmobile	7665
10	2003	Buick	14950
11	2001	Chevrolet	9260
12	2001	DodgeTruck	10030
13	2003	Ford	14150
14	2001	Volkswagen	9530
15	2002	SaturnTruck	14605
Sheet20 (2)

You will notice my results do not agree with what you have posted for all the cars -- so you may want to check that.

 

[Yogi Anand]

Hi JJ:

And here is a solution using DATA Table ...

y040222h1a.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Make	0~10	10~11	11~12	12~13	13~14	14~15	15~9.9E+304		Yearf	Pricef	Makef
2		2004	0	0	0	0	0	0	0		TRUE	TRUE	TRUE
3		2003	0	0	0	0	0	2	0
4		2002	0	0	0	0	0	1	0		2001	0~10
5		2001	3	1	0	0	0	0	0				key-instringforMake leaveM4blank forall
6		2000	1	0	0	0	0	0	0			3
7	Year	Make	Price
8	2001	Saturn	9596
9	2000	Oldsmobile	7665
10	2003	Buick	14950
11	2001	Chevrolet	9260
12	2001	DodgeTruck	10030
13	2003	Ford	14150
14	2001	Volkswagen	9530
15	2002	SaturnTruck	14605
Sheet20

 

[JJ in SD]

Thanks tactps and Yogi for your help.

Yogi, was working on tactps solution before I looked at your post and it works well (posted below). I tried your first post and could not get anything except #VALUE, obviously I was doing something wrong.... I will post after this with an additional question on tactps formula. I tried to modify it some to look for additional information. Anyways this is the result of tactps answer. Thanks again guys, JJ

AFOCUS-Used_October_03.XLS
	A	B	C	D	E	F	G	H	I
1			<10K	$10K-$11K	$11-$12K	$12-$13K	$13K-$14K	$14-$15K	>$15K
2	2004	0	0	0	0	0	0	0	0
3	2003	2	0	0	0	0	0	2	0
4	2002	0	0	0	0	0	0	0	0
5	2001	3	2	1	0	0	0	0	0
6	2000	1	1	0	0	0	0	0	0
7		6
8		2001	Saturn	9596
9		2000	Oldsmobile	7665
10		2003	Buick	14950
11		2001	Chevrolet	9260
12		2001	DodgeTruck	10030
13		2003	Ford	14150
14		2001	Volkswagen	9530
Sheet1