# Finding Price Points

#### JJ in SD

##### Active Member
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?

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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### tactps

##### Well-known Member
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
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
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
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

Replies
0
Views
182
Replies
8
Views
1K
Replies
1
Views
220
Replies
3
Views
800
Replies
1
Views
1K

1,187,188
Messages
5,962,102
Members
438,584
Latest member
MASV

### 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.

### Which adblocker are you using?

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

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