Finding Price Points

JJ in SD

Active Member
Joined
Jul 30, 2002
Messages
316
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
ABCDEFGHIJ
1<$10K$10-$11K$11-$12K$12-$13K$13K-$14K$14-$15K>$15K
220040
3200322
420020
52001321
6200011
76
82001Saturn9596
92000Oldsmobile7665
102003Buick14950
112001Chevrolet9260
122001DodgeTruck10030
132003Ford14150
142001Volkswagen9530
152002SaturnTruck14605
Sheet1
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFGHI
10~1010~1111~1212~1313~1414~1515~9.9E+304
220040000000
320030000020
420020000010
520013100000
620001000000
7
82001Saturn9596
92000Oldsmobile7665
102003Buick14950
112001Chevrolet9260
122001DodgeTruck10030
132003Ford14150
142001Volkswagen9530
152002SaturnTruck14605
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi JJ:

And here is a solution using DATA Table ...
y040222h1a.xls
ABCDEFGHIJKLM
1Make0~1010~1111~1212~1313~1414~1515~9.9E+304YearfPricefMakef
220040000000TRUETRUETRUE
320030000020
42002000001020010~10
520013100000key-instringforMake leaveM4blank forall
6200010000003
7YearMakePrice
82001Saturn9596
92000Oldsmobile7665
102003Buick14950
112001Chevrolet9260
122001DodgeTruck10030
132003Ford14150
142001Volkswagen9530
152002SaturnTruck14605
Sheet20
 

JJ in SD

Active Member
Joined
Jul 30, 2002
Messages
316
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
ABCDEFGHI
1<10K$10K-$11K$11-$12K$12-$13K$13K-$14K$14-$15K>$15K
2200400000000
3200320000020
4200200000000
5200132100000
6200011000000
76
82001Saturn9596
92000Oldsmobile7665
102003Buick14950
112001Chevrolet9260
122001DodgeTruck10030
132003Ford14150
142001Volkswagen9530
Sheet1
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,452
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top