2

Good day all. Have a headache worksheet.Plus I am something of a newbie at excel. I have a sheet with varying ranges. Each value that is to be displayed, depends on a value entered in two other cells. For example: 

Cell A14 = Item Description (drop down list)
Cell A18 = Item Weight
Cell C15 = Cost per Item (this varies depending on description & weight)
Cell D15 = Final calculated cost

In Cell C15, some items have anywhere from 3 to 6 different values/costs depending on the weight & description of the item being ordered. eg: One item range & cost:

1-44 = 1.52 per item
45-299 = 1.29 per item
300-499 = 1.25 per item
500-999 = 1.11 per item
1000- = 0.92 per item

While another costs:

1-44 = 13.02 per item
45-99 = 4.02 per item
100-299 = 3.87 per item
300-499 = 3.70 per item
500-  = 3.59 per item

Keeping in mind as well that there are 13 different items, how do I get cell C5 to show the corresponding value depending on the item description in cell A14 and item weight in cell A18 ? I have gotten as far as cell d5 where it can calculate based on the item description with a Vlookup formula: 

=IF(A19*C15<VLOOKUP(A14,Sheet4!$B$1:$D$26,3,0),VLOOKUP(A14,Sheet4!$B$1:$D$26,3,0),A19*C15)

I have also created other sheets (1 & 4) that have the other other ranges. Would this be an IF function, another VLookup or a combination of both?

Thanks in advance.

Seems I dont have enough reps to post an image.

flavius
  • 21
  • 2

1 Answers1

2

You can use sumproduct:

Example 1

Example 2

Just set up your table with the max and min ranges and the correspoding amounts.

ADDENDUM:

Here is how you would do it with SUMIFS()

Example 3

Example 4

Stepan1010
  • 3,136
  • 1
  • 16
  • 21