Excel 2007 - find max value based on status criteria in data list
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I need to find the largest value (aging days) in a set of data based on the item status. Much like a SUMIF function, but a MAXIF (if one existed). My data is not sorted by status. I basically want to pull out the oldest age date per category. I've tried several formulas I've found on the site with similar data elements, but cannot get any of them to work.
microsoft-excel worksheet-function
asked Aug 4 '14 at 20:03
SueSue
111
add a comment |
I need to find the largest value (aging days) in a set of data based on the item status. Much like a SUMIF function, but a MAXIF (if one existed). My data is not sorted by status. I basically want to pull out the oldest age date per category. I've tried several formulas I've found on the site with similar data elements, but cannot get any of them to work.
microsoft-excel worksheet-function
asked Aug 4 '14 at 20:03
SueSue
111
1
Related: superuser.com/q/728576/76571
– Excellll
Aug 4 '14 at 20:09
1
Related also: stackoverflow.com/questions/1274788/…
– Dan Smith
Aug 4 '14 at 20:10
add a comment |
I need to find the largest value (aging days) in a set of data based on the item status. Much like a SUMIF function, but a MAXIF (if one existed). My data is not sorted by status. I basically want to pull out the oldest age date per category. I've tried several formulas I've found on the site with similar data elements, but cannot get any of them to work.
microsoft-excel worksheet-function
asked Aug 4 '14 at 20:03
SueSue
111
I need to find the largest value (aging days) in a set of data based on the item status. Much like a SUMIF function, but a MAXIF (if one existed). My data is not sorted by status. I basically want to pull out the oldest age date per category. I've tried several formulas I've found on the site with similar data elements, but cannot get any of them to work.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Aug 4 '14 at 20:03
SueSue
111
asked Aug 4 '14 at 20:03
SueSue
111
asked Aug 4 '14 at 20:03
SueSue
111
asked Aug 4 '14 at 20:03
SueSue
111
asked Aug 4 '14 at 20:03
SueSue
111
111
1
Related: superuser.com/q/728576/76571
– Excellll
Aug 4 '14 at 20:09
1
Related also: stackoverflow.com/questions/1274788/…
– Dan Smith
Aug 4 '14 at 20:10
add a comment |
1
Related: superuser.com/q/728576/76571
– Excellll
Aug 4 '14 at 20:09
1
Related also: stackoverflow.com/questions/1274788/…
– Dan Smith
Aug 4 '14 at 20:10
1
1
Related: superuser.com/q/728576/76571
– Excellll
Aug 4 '14 at 20:09
Related: superuser.com/q/728576/76571
– Excellll
Aug 4 '14 at 20:09
1
1
Related also: stackoverflow.com/questions/1274788/…
– Dan Smith
Aug 4 '14 at 20:10
Related also: stackoverflow.com/questions/1274788/…
– Dan Smith
Aug 4 '14 at 20:10
add a comment |
1 Answer
1
active
oldest
votes
Type in
=Max(If($A$1:$A$40=A1,$B$1:$B$40))
and then present Ctrl+Shift+Enter to array enter the formula. Update A1:A40 to your lookup range, A1 to your lookup value, and B1:B40 to your max range.
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
This is not working. My data is on another tab, and I updated the formula to reflect that, but I keep getting a syntax error. =Max(If('PGI_8-3-2014'!$k$2:$k$3237=A2,'PGI_8-3-2014'!$n$2:$n$3237))
– Sue
Aug 5 '14 at 12:46
Did you type the formula and copy and paste, or did you actually select the range?
– cronos2546
Aug 8 '14 at 14:45
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f792400%2fexcel-2007-find-max-value-based-on-status-criteria-in-data-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Type in
=Max(If($A$1:$A$40=A1,$B$1:$B$40))
and then present Ctrl+Shift+Enter to array enter the formula. Update A1:A40 to your lookup range, A1 to your lookup value, and B1:B40 to your max range.
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
This is not working. My data is on another tab, and I updated the formula to reflect that, but I keep getting a syntax error. =Max(If('PGI_8-3-2014'!$k$2:$k$3237=A2,'PGI_8-3-2014'!$n$2:$n$3237))
– Sue
Aug 5 '14 at 12:46
Did you type the formula and copy and paste, or did you actually select the range?
– cronos2546
Aug 8 '14 at 14:45
add a comment |
Type in
=Max(If($A$1:$A$40=A1,$B$1:$B$40))
and then present Ctrl+Shift+Enter to array enter the formula. Update A1:A40 to your lookup range, A1 to your lookup value, and B1:B40 to your max range.
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
This is not working. My data is on another tab, and I updated the formula to reflect that, but I keep getting a syntax error. =Max(If('PGI_8-3-2014'!$k$2:$k$3237=A2,'PGI_8-3-2014'!$n$2:$n$3237))
– Sue
Aug 5 '14 at 12:46
Did you type the formula and copy and paste, or did you actually select the range?
– cronos2546
Aug 8 '14 at 14:45
add a comment |
Type in
=Max(If($A$1:$A$40=A1,$B$1:$B$40))
and then present Ctrl+Shift+Enter to array enter the formula. Update A1:A40 to your lookup range, A1 to your lookup value, and B1:B40 to your max range.
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
Type in
=Max(If($A$1:$A$40=A1,$B$1:$B$40))
and then present Ctrl+Shift+Enter to array enter the formula. Update A1:A40 to your lookup range, A1 to your lookup value, and B1:B40 to your max range.
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
answered Aug 4 '14 at 20:19
cronos2546cronos2546
1112
1112
This is not working. My data is on another tab, and I updated the formula to reflect that, but I keep getting a syntax error. =Max(If('PGI_8-3-2014'!$k$2:$k$3237=A2,'PGI_8-3-2014'!$n$2:$n$3237))
– Sue
Aug 5 '14 at 12:46
Did you type the formula and copy and paste, or did you actually select the range?
– cronos2546
Aug 8 '14 at 14:45
add a comment |
This is not working. My data is on another tab, and I updated the formula to reflect that, but I keep getting a syntax error. =Max(If('PGI_8-3-2014'!$k$2:$k$3237=A2,'PGI_8-3-2014'!$n$2:$n$3237))
– Sue
Aug 5 '14 at 12:46
Did you type the formula and copy and paste, or did you actually select the range?
– cronos2546
Aug 8 '14 at 14:45
This is not working. My data is on another tab, and I updated the formula to reflect that, but I keep getting a syntax error. =Max(If('PGI_8-3-2014'!$k$2:$k$3237=A2,'PGI_8-3-2014'!$n$2:$n$3237))
– Sue
Aug 5 '14 at 12:46
This is not working. My data is on another tab, and I updated the formula to reflect that, but I keep getting a syntax error. =Max(If('PGI_8-3-2014'!$k$2:$k$3237=A2,'PGI_8-3-2014'!$n$2:$n$3237))
– Sue
Aug 5 '14 at 12:46
Did you type the formula and copy and paste, or did you actually select the range?
– cronos2546
Aug 8 '14 at 14:45
Did you type the formula and copy and paste, or did you actually select the range?
– cronos2546
Aug 8 '14 at 14:45
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f792400%2fexcel-2007-find-max-value-based-on-status-criteria-in-data-list%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
1
Related: superuser.com/q/728576/76571
– Excellll
Aug 4 '14 at 20:09
1
Related also: stackoverflow.com/questions/1274788/…
– Dan Smith
Aug 4 '14 at 20:10