Excel Summation of Multiple Conditional Maximum Values
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I am working on getting pricing based on the number of units we are ordering using Excel sumifs. The data I have looks something like this:
A B C D
Item1 Comp1 1 4.99
Item1 Comp1 10 3.99
Item1 Comp1 100 2.99
Item1 Comp2 1 13.99
Item1 Comp2 100 10.99
Item1 Comp3 1 2.99
Item1 Comp3 10 2.59
Item1 Comp3 50 2.19
Item1 Comp3 100 1.99
... ... ... ...
Where column A is the main item, column B is the individual components of the item in column A, and column C is the number we need to order in order to get the price listed in column D.
In a separate sheet, I have the following table:
A B C
Item1 10 FORMULA
Item2 5 FORMULA
Item3 20 FORMULA
... ... ...
The point of this sheet is to have the Item name as seen in Column A of the first table, column B holds the number we need to order, and column C (hopefully) lists the total price by adding all the components at their respective price breaks.
In this example, the sum for Item1 I am looking for is 3.99 + 13.99 + 2.59 = 20.57 because 10 items gets the 10 price break for component 1, the 1 price break for component 2, and the 10 price break for component 3.
So far I am able to sum the cost based on the item name in column C:
=SUMIFS(Table1[D], Table1[A], "="A2)
I am having trouble starting the second part which is basically only summing the maximum price break for each component where Table1[C] <= B2.
excel sumifs
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
add a comment |
I am working on getting pricing based on the number of units we are ordering using Excel sumifs. The data I have looks something like this:
A B C D
Item1 Comp1 1 4.99
Item1 Comp1 10 3.99
Item1 Comp1 100 2.99
Item1 Comp2 1 13.99
Item1 Comp2 100 10.99
Item1 Comp3 1 2.99
Item1 Comp3 10 2.59
Item1 Comp3 50 2.19
Item1 Comp3 100 1.99
... ... ... ...
Where column A is the main item, column B is the individual components of the item in column A, and column C is the number we need to order in order to get the price listed in column D.
In a separate sheet, I have the following table:
A B C
Item1 10 FORMULA
Item2 5 FORMULA
Item3 20 FORMULA
... ... ...
The point of this sheet is to have the Item name as seen in Column A of the first table, column B holds the number we need to order, and column C (hopefully) lists the total price by adding all the components at their respective price breaks.
In this example, the sum for Item1 I am looking for is 3.99 + 13.99 + 2.59 = 20.57 because 10 items gets the 10 price break for component 1, the 1 price break for component 2, and the 10 price break for component 3.
So far I am able to sum the cost based on the item name in column C:
=SUMIFS(Table1[D], Table1[A], "="A2)
I am having trouble starting the second part which is basically only summing the maximum price break for each component where Table1[C] <= B2.
excel sumifs
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
So, for your example data, the correct result would be 4.99 + 13.99 + 2.99 or 21.97?
– StoneGiant
Nov 15 '18 at 16:39
1
@StoneGiant Good point, I have updated my question to show the actual correct result would be 3.99 + 13.99 + 2.59 or 20.57
– bagelmakers
Nov 15 '18 at 16:59
Do you have Office 365?
– XOR LX
Nov 15 '18 at 22:49
add a comment |
I am working on getting pricing based on the number of units we are ordering using Excel sumifs. The data I have looks something like this:
A B C D
Item1 Comp1 1 4.99
Item1 Comp1 10 3.99
Item1 Comp1 100 2.99
Item1 Comp2 1 13.99
Item1 Comp2 100 10.99
Item1 Comp3 1 2.99
Item1 Comp3 10 2.59
Item1 Comp3 50 2.19
Item1 Comp3 100 1.99
... ... ... ...
Where column A is the main item, column B is the individual components of the item in column A, and column C is the number we need to order in order to get the price listed in column D.
In a separate sheet, I have the following table:
A B C
Item1 10 FORMULA
Item2 5 FORMULA
Item3 20 FORMULA
... ... ...
The point of this sheet is to have the Item name as seen in Column A of the first table, column B holds the number we need to order, and column C (hopefully) lists the total price by adding all the components at their respective price breaks.
In this example, the sum for Item1 I am looking for is 3.99 + 13.99 + 2.59 = 20.57 because 10 items gets the 10 price break for component 1, the 1 price break for component 2, and the 10 price break for component 3.
So far I am able to sum the cost based on the item name in column C:
=SUMIFS(Table1[D], Table1[A], "="A2)
I am having trouble starting the second part which is basically only summing the maximum price break for each component where Table1[C] <= B2.
excel sumifs
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
I am working on getting pricing based on the number of units we are ordering using Excel sumifs. The data I have looks something like this:
A B C D
Item1 Comp1 1 4.99
Item1 Comp1 10 3.99
Item1 Comp1 100 2.99
Item1 Comp2 1 13.99
Item1 Comp2 100 10.99
Item1 Comp3 1 2.99
Item1 Comp3 10 2.59
Item1 Comp3 50 2.19
Item1 Comp3 100 1.99
... ... ... ...
Where column A is the main item, column B is the individual components of the item in column A, and column C is the number we need to order in order to get the price listed in column D.
In a separate sheet, I have the following table:
A B C
Item1 10 FORMULA
Item2 5 FORMULA
Item3 20 FORMULA
... ... ...
The point of this sheet is to have the Item name as seen in Column A of the first table, column B holds the number we need to order, and column C (hopefully) lists the total price by adding all the components at their respective price breaks.
In this example, the sum for Item1 I am looking for is 3.99 + 13.99 + 2.59 = 20.57 because 10 items gets the 10 price break for component 1, the 1 price break for component 2, and the 10 price break for component 3.
So far I am able to sum the cost based on the item name in column C:
=SUMIFS(Table1[D], Table1[A], "="A2)
I am having trouble starting the second part which is basically only summing the maximum price break for each component where Table1[C] <= B2.
excel sumifs
excel sumifs
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
edited Nov 15 '18 at 17:02
bagelmakers
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
asked Nov 15 '18 at 16:23
bagelmakersbagelmakers
304112
304112
So, for your example data, the correct result would be 4.99 + 13.99 + 2.99 or 21.97?
– StoneGiant
Nov 15 '18 at 16:39
1
@StoneGiant Good point, I have updated my question to show the actual correct result would be 3.99 + 13.99 + 2.59 or 20.57
– bagelmakers
Nov 15 '18 at 16:59
Do you have Office 365?
– XOR LX
Nov 15 '18 at 22:49
add a comment |
So, for your example data, the correct result would be 4.99 + 13.99 + 2.99 or 21.97?
– StoneGiant
Nov 15 '18 at 16:39
1
@StoneGiant Good point, I have updated my question to show the actual correct result would be 3.99 + 13.99 + 2.59 or 20.57
– bagelmakers
Nov 15 '18 at 16:59
Do you have Office 365?
– XOR LX
Nov 15 '18 at 22:49
So, for your example data, the correct result would be 4.99 + 13.99 + 2.99 or 21.97?
– StoneGiant
Nov 15 '18 at 16:39
So, for your example data, the correct result would be 4.99 + 13.99 + 2.99 or 21.97?
– StoneGiant
Nov 15 '18 at 16:39
1
1
@StoneGiant Good point, I have updated my question to show the actual correct result would be 3.99 + 13.99 + 2.59 or 20.57
– bagelmakers
Nov 15 '18 at 16:59
@StoneGiant Good point, I have updated my question to show the actual correct result would be 3.99 + 13.99 + 2.59 or 20.57
– bagelmakers
Nov 15 '18 at 16:59
Do you have Office 365?
– XOR LX
Nov 15 '18 at 22:49
Do you have Office 365?
– XOR LX
Nov 15 '18 at 22:49
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323786%2fexcel-summation-of-multiple-conditional-maximum-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323786%2fexcel-summation-of-multiple-conditional-maximum-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So, for your example data, the correct result would be 4.99 + 13.99 + 2.99 or 21.97?
– StoneGiant
Nov 15 '18 at 16:39
1
@StoneGiant Good point, I have updated my question to show the actual correct result would be 3.99 + 13.99 + 2.59 or 20.57
– bagelmakers
Nov 15 '18 at 16:59
Do you have Office 365?
– XOR LX
Nov 15 '18 at 22:49