Maybe SUMIFS with greater than or less than?

cb123

New Member
Joined
Jul 1, 2016
Messages
34
Hi there, hopefully someone can help. I'm not knowledgeable to a deep level on excel but know what i need to achieve.

Say i have a data validation drop down list of fruits in cells A2-A8 and then the weights (manually entered) of those fruits next to them in B2-B8. Then the quantity of fruits which weight the same in C2-C8. Some fruits may appear more than once in the A2-A8 list because their weight is unique.

I would like to sum all oranges, for example, which weigh less than X and then multiply them by a unit price and quantity, then sum all oranges which weigh more than X and multiply them by a price and quantity and have the final price return in a cell. There are a max of 4 different price tiers which are weight dependant.

Many thanks in advance,

All the best.

Ben
 

Attachments

  • Excel example.jpg
    Excel example.jpg
    50.8 KB · Views: 13

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
With one SUMIFS you can only realize AND-relations.
With two SUMIFS you can realize OR-relations
=SUMIFS(what you want for <10)*price+SUMIFS(what you want for >=10)*price
 
Upvote 0
You need sumproduct, not sumifs, the post above contains bad advice. With the price outside of the function it would not be bound by the criteria.
Excel Formula:
=SUMPRODUCT((A2:A10="Apple")*(B2:B10<10),C2:C10)
For tiers that are between a lower and upper limit, you would need to use 2 arrays for that range.
 
Upvote 0
@jasonb75: What is wrong on this solution?

Map3
ABCDEF
1fruitweightquantity
2apple101
3apple91
4apple81
5orange102<101
6orange91>=102
7orange81
8banana151
9
10
11
12orange6
Blad1
Cell Formulas
RangeFormula
D12D12=SUMIFS(C2:C8,A2:A8,C12,B2:B8,"<10")*F5+SUMIFS(C2:C8,A2:A8,C12,B2:B8,">=10")*F6
 
Upvote 0
the post above contains bad advice
That should say, 'The post above post 4 contains bad advice' :oops:

Freudian slip of the eyeballs when I read the question, I saw one thing and thought a mother.
 
Upvote 0
You need sumproduct, not sumifs, the post above contains bad advice. With the price outside of the function it would not be bound by the criteria.
Excel Formula:
=SUMPRODUCT((A2:A10="Apple")*(B2:B10<10),C2:C10)
For tiers that are between a lower and upper limit, you would need to use 2 arrays for that range.
Thanks that's super helpful and getting me close. Would you mind explaining a little more on the below

"For tiers that are between a lower and upper limit, you would need to use 2 arrays for that range."

How would i do that if my apples have 4 weight tiers?

Many thanks,

Ben
 
Upvote 0
There was an error in how I read what was needed, see the other replies in the thread ;)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top