# Summing Based on Range

#### LEGION

##### New Member
Dear All,

I need to do the following:

I have a price increase for item 12345 for

a.) 100 - 200 = 1.5\$
b.) 200 - 300 = 1.4\$
c.) 300 - 400 = 1.3\$

I have a history for 12345 from the last year where sometimes it was ordered in the following quantities:

1.) 100 (would be 1.5)
2.) 200 (would be 1.4)
3.) 305 (would be ???)

The thing here is, I can not use sumproduct() for history values that are different from the standard quantities defined under a.b.c.

Can the sumproduct be changed to also allow checking values which are different from the standard values?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### LEGION

##### New Member
Currently, my formula looks like this:

PHP:
``=SUMPRODUCT((priceinc!\$A\$2:\$A\$45=ser_3396!\$F2)*(priceinc!\$B\$2:\$B\$45=ser_3396!\$G2)*(priceinc!\$C\$2:\$C\$45))``

Of course, the problem being here the second part priceinc!\$B\$2:\$B\$45=ser_3396!\$G2 ... because of the = it will only return values which are exactly the same quantity as the standard quantities.

When I use <= it will sum all the other defined standard values together, based on how large the actual history value is.

It only works, atm, for those quantities ordered which are exactly the same as the defined standard values (quantities).

##### MrExcel MVP
Try to provide a small sample along with the desired result.

Replies
1
Views
426
Replies
11
Views
237
Replies
6
Views
138
Replies
9
Views
252
Replies
9
Views
246

1,191,134
Messages
5,984,870
Members
439,921
Latest member
Neocold

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