Excel SUMIF criteria reference
SUMIF function - Office Support only says:
criteria Required. The criteria in the form of a number, expression,
a cell reference, text, or a function that defines which cells will be
added. For example, criteria can be expressed as 32, ">32", B5, "32",
"apples", or TODAY().
Which gives absolutely no clue about what are and aren't possible inputs and what their syntax and semantics would be. (E.g. if it's a function or expression, how do I mark the point where the cell's value should be inserted?)
3rd-party sites, e.g. https://exceljet.net/excel-functions/excel-sumif-function and https://www.myonlinetraininghub.com/excel-wildcards-in-your-sumif-countif-and-vlookup, give some examples of possible inputs -- which apparently include wildcards (with nonstandard markup to boot) that the doc says nothing about whatsoever. But they cannot guarantee that their list is complete, nor which Excel version(s) each item is applicable to.
Is any more conclusive documentation available?
Clarification: I'm asking specifically about official sources (only they can be called "documentation", but apparently this was unclear). I need to be sure that I have complete and accurate information, and no 3rd-party resource can guarantee that (unless it's a result of reverse engineering the corresponding Excel functionality or something).
E.g. I suspect that this "criterion" functionality is common for several Excel functions, and there's some dedicated article on it that I could easily find if only I knew the keywords to look for (nothing of the kind is found by "criteria").
microsoft-excel worksheet-function
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
add a comment |
SUMIF function - Office Support only says:
criteria Required. The criteria in the form of a number, expression,
a cell reference, text, or a function that defines which cells will be
added. For example, criteria can be expressed as 32, ">32", B5, "32",
"apples", or TODAY().
Which gives absolutely no clue about what are and aren't possible inputs and what their syntax and semantics would be. (E.g. if it's a function or expression, how do I mark the point where the cell's value should be inserted?)
3rd-party sites, e.g. https://exceljet.net/excel-functions/excel-sumif-function and https://www.myonlinetraininghub.com/excel-wildcards-in-your-sumif-countif-and-vlookup, give some examples of possible inputs -- which apparently include wildcards (with nonstandard markup to boot) that the doc says nothing about whatsoever. But they cannot guarantee that their list is complete, nor which Excel version(s) each item is applicable to.
Is any more conclusive documentation available?
Clarification: I'm asking specifically about official sources (only they can be called "documentation", but apparently this was unclear). I need to be sure that I have complete and accurate information, and no 3rd-party resource can guarantee that (unless it's a result of reverse engineering the corresponding Excel functionality or something).
E.g. I suspect that this "criterion" functionality is common for several Excel functions, and there's some dedicated article on it that I could easily find if only I knew the keywords to look for (nothing of the kind is found by "criteria").
microsoft-excel worksheet-function
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
SUMIF is basically Conditional SUM and to execute the Formula a proper combination using Logical & Relational Operates is required, Wild Card makes it more flexible with Criteria, ☺
– Rajesh S
Dec 18 '18 at 7:40
@fixer1234 I'm asking specifically about official documentation or other official sources. As I explained, random "learning materials" are not good enough for my needs because they can't guarantee accuracy and completeness.
– ivan_pozdeev
Dec 18 '18 at 8:41
"There isn't really a more comprehensive spec because that covers everything." I gave two cases that it doesn't cover. Who knows how many more there are.
– ivan_pozdeev
Dec 18 '18 at 9:58
Your question seems to stem from there appearing to be different partial information at different sources, including details at 3rd party sites that you didn't see at the Microsoft site. Actually, all of that was at the Microsoft site. Also, the relevant syntax rules are fully explained. Examples are available at your linked sites. That's the complete, official description. I posted an answer that added a little more explanation. Basically, you can do anything that falls within that description, and the syntax isn't complex. Beyond that, it isn't clear what you think isn't clear. (cont'd)
– fixer1234
Dec 18 '18 at 11:55
If you think you might still be missing some options, that's all there is. If you're unsure how to implement a specific example, including something you think isn't covered by the definition, that would make a good question and be something specific that people can respond to.
– fixer1234
Dec 18 '18 at 11:55
add a comment |
SUMIF function - Office Support only says:
criteria Required. The criteria in the form of a number, expression,
a cell reference, text, or a function that defines which cells will be
added. For example, criteria can be expressed as 32, ">32", B5, "32",
"apples", or TODAY().
Which gives absolutely no clue about what are and aren't possible inputs and what their syntax and semantics would be. (E.g. if it's a function or expression, how do I mark the point where the cell's value should be inserted?)
3rd-party sites, e.g. https://exceljet.net/excel-functions/excel-sumif-function and https://www.myonlinetraininghub.com/excel-wildcards-in-your-sumif-countif-and-vlookup, give some examples of possible inputs -- which apparently include wildcards (with nonstandard markup to boot) that the doc says nothing about whatsoever. But they cannot guarantee that their list is complete, nor which Excel version(s) each item is applicable to.
Is any more conclusive documentation available?
Clarification: I'm asking specifically about official sources (only they can be called "documentation", but apparently this was unclear). I need to be sure that I have complete and accurate information, and no 3rd-party resource can guarantee that (unless it's a result of reverse engineering the corresponding Excel functionality or something).
E.g. I suspect that this "criterion" functionality is common for several Excel functions, and there's some dedicated article on it that I could easily find if only I knew the keywords to look for (nothing of the kind is found by "criteria").
microsoft-excel worksheet-function
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
SUMIF function - Office Support only says:
criteria Required. The criteria in the form of a number, expression,
a cell reference, text, or a function that defines which cells will be
added. For example, criteria can be expressed as 32, ">32", B5, "32",
"apples", or TODAY().
Which gives absolutely no clue about what are and aren't possible inputs and what their syntax and semantics would be. (E.g. if it's a function or expression, how do I mark the point where the cell's value should be inserted?)
3rd-party sites, e.g. https://exceljet.net/excel-functions/excel-sumif-function and https://www.myonlinetraininghub.com/excel-wildcards-in-your-sumif-countif-and-vlookup, give some examples of possible inputs -- which apparently include wildcards (with nonstandard markup to boot) that the doc says nothing about whatsoever. But they cannot guarantee that their list is complete, nor which Excel version(s) each item is applicable to.
Is any more conclusive documentation available?
Clarification: I'm asking specifically about official sources (only they can be called "documentation", but apparently this was unclear). I need to be sure that I have complete and accurate information, and no 3rd-party resource can guarantee that (unless it's a result of reverse engineering the corresponding Excel functionality or something).
E.g. I suspect that this "criterion" functionality is common for several Excel functions, and there's some dedicated article on it that I could easily find if only I knew the keywords to look for (nothing of the kind is found by "criteria").
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
edited Dec 18 '18 at 8:49
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
asked Dec 18 '18 at 6:45
ivan_pozdeev
1,130722
1,130722
SUMIF is basically Conditional SUM and to execute the Formula a proper combination using Logical & Relational Operates is required, Wild Card makes it more flexible with Criteria, ☺
– Rajesh S
Dec 18 '18 at 7:40
@fixer1234 I'm asking specifically about official documentation or other official sources. As I explained, random "learning materials" are not good enough for my needs because they can't guarantee accuracy and completeness.
– ivan_pozdeev
Dec 18 '18 at 8:41
"There isn't really a more comprehensive spec because that covers everything." I gave two cases that it doesn't cover. Who knows how many more there are.
– ivan_pozdeev
Dec 18 '18 at 9:58
Your question seems to stem from there appearing to be different partial information at different sources, including details at 3rd party sites that you didn't see at the Microsoft site. Actually, all of that was at the Microsoft site. Also, the relevant syntax rules are fully explained. Examples are available at your linked sites. That's the complete, official description. I posted an answer that added a little more explanation. Basically, you can do anything that falls within that description, and the syntax isn't complex. Beyond that, it isn't clear what you think isn't clear. (cont'd)
– fixer1234
Dec 18 '18 at 11:55
If you think you might still be missing some options, that's all there is. If you're unsure how to implement a specific example, including something you think isn't covered by the definition, that would make a good question and be something specific that people can respond to.
– fixer1234
Dec 18 '18 at 11:55
add a comment |
SUMIF is basically Conditional SUM and to execute the Formula a proper combination using Logical & Relational Operates is required, Wild Card makes it more flexible with Criteria, ☺
– Rajesh S
Dec 18 '18 at 7:40
@fixer1234 I'm asking specifically about official documentation or other official sources. As I explained, random "learning materials" are not good enough for my needs because they can't guarantee accuracy and completeness.
– ivan_pozdeev
Dec 18 '18 at 8:41
"There isn't really a more comprehensive spec because that covers everything." I gave two cases that it doesn't cover. Who knows how many more there are.
– ivan_pozdeev
Dec 18 '18 at 9:58
Your question seems to stem from there appearing to be different partial information at different sources, including details at 3rd party sites that you didn't see at the Microsoft site. Actually, all of that was at the Microsoft site. Also, the relevant syntax rules are fully explained. Examples are available at your linked sites. That's the complete, official description. I posted an answer that added a little more explanation. Basically, you can do anything that falls within that description, and the syntax isn't complex. Beyond that, it isn't clear what you think isn't clear. (cont'd)
– fixer1234
Dec 18 '18 at 11:55
If you think you might still be missing some options, that's all there is. If you're unsure how to implement a specific example, including something you think isn't covered by the definition, that would make a good question and be something specific that people can respond to.
– fixer1234
Dec 18 '18 at 11:55
SUMIF is basically Conditional SUM and to execute the Formula a proper combination using Logical & Relational Operates is required, Wild Card makes it more flexible with Criteria, ☺
– Rajesh S
Dec 18 '18 at 7:40
SUMIF is basically Conditional SUM and to execute the Formula a proper combination using Logical & Relational Operates is required, Wild Card makes it more flexible with Criteria, ☺
– Rajesh S
Dec 18 '18 at 7:40
@fixer1234 I'm asking specifically about official documentation or other official sources. As I explained, random "learning materials" are not good enough for my needs because they can't guarantee accuracy and completeness.
– ivan_pozdeev
Dec 18 '18 at 8:41
@fixer1234 I'm asking specifically about official documentation or other official sources. As I explained, random "learning materials" are not good enough for my needs because they can't guarantee accuracy and completeness.
– ivan_pozdeev
Dec 18 '18 at 8:41
"There isn't really a more comprehensive spec because that covers everything." I gave two cases that it doesn't cover. Who knows how many more there are.
– ivan_pozdeev
Dec 18 '18 at 9:58
"There isn't really a more comprehensive spec because that covers everything." I gave two cases that it doesn't cover. Who knows how many more there are.
– ivan_pozdeev
Dec 18 '18 at 9:58
Your question seems to stem from there appearing to be different partial information at different sources, including details at 3rd party sites that you didn't see at the Microsoft site. Actually, all of that was at the Microsoft site. Also, the relevant syntax rules are fully explained. Examples are available at your linked sites. That's the complete, official description. I posted an answer that added a little more explanation. Basically, you can do anything that falls within that description, and the syntax isn't complex. Beyond that, it isn't clear what you think isn't clear. (cont'd)
– fixer1234
Dec 18 '18 at 11:55
Your question seems to stem from there appearing to be different partial information at different sources, including details at 3rd party sites that you didn't see at the Microsoft site. Actually, all of that was at the Microsoft site. Also, the relevant syntax rules are fully explained. Examples are available at your linked sites. That's the complete, official description. I posted an answer that added a little more explanation. Basically, you can do anything that falls within that description, and the syntax isn't complex. Beyond that, it isn't clear what you think isn't clear. (cont'd)
– fixer1234
Dec 18 '18 at 11:55
If you think you might still be missing some options, that's all there is. If you're unsure how to implement a specific example, including something you think isn't covered by the definition, that would make a good question and be something specific that people can respond to.
– fixer1234
Dec 18 '18 at 11:55
If you think you might still be missing some options, that's all there is. If you're unsure how to implement a specific example, including something you think isn't covered by the definition, that would make a good question and be something specific that people can respond to.
– fixer1234
Dec 18 '18 at 11:55
add a comment |
1 Answer
1
active
oldest
votes
The complete specification for the SUMIF criteria, including syntax, appears to be:
- A numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2018, TRUE) or
- A cell reference or
- A text string (e.g. “Text”, “Thursday”) or
- A function (e.g.,
TODAY()) or - An expression (e.g. “>12”, “<>0”).
- Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is a numeric value, double quotation marks are not required.
- You can use the wildcard characters--the question mark (?) and asterisk (*)--in the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
That appears to be the entire, comprehensive description. Some sources are a little more descriptive than others for specific items. I compared numerous sources against the Microsoft documentation. This contains what looks like the most descriptive versions of the components, which came mostly from Microsoft Support and Corporate Finance Institute. The Microsoft Support link does contain everything, including the wildcard information.
Regarding which versions of Excel this applies to, I don't believe any of this has changed, at least in any version likely to still be in use (i.e., going back at least to v2007).
There are some things you can't do with SUMIF. They aren't included in the list of what you can do, but just for completeness:
- SUMIF is not one of the Excel functions for which the criteria can use array constants.
- You cannot use SUMIF with logical Boolean operators, such as OR and AND.
- When you want to use multiple conditions, array constants, nested IF statements or Boolean operators; or you want one total for multiple conditions (criteria); use the two functions SUM and IF together. For example, use SUM(IF()) instead of the SUMIF() function.
See Microsoft Support
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
So, how do I use an expression where the value shall go somewhere in the middle or into multiple places? Are wildcards applicable anywhere except plain text (e.g. in cell addresses, parts of an expression to evaluate)? How is plain text differentiated from an expression? <-- does anything cover these parts?
– ivan_pozdeev
Dec 18 '18 at 13:59
@ivan_pozdeev, Those things are not specific to SUMIF, they apply to anything you do in Excel, which is why they aren't described under SUMIF. The first item (value placement), it isn't clear what specific situation you're asking about. Can you add a pseudo-code example, or several if you're referring to a collection of cases? Basically, each use of SUMIF needs to follow the rules, and any component of a SUMIF needs to follow the rules, so I'm not understanding that part. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
Wildcards only ever apply to text strings, and you can use them only for making comparisons of text strings, like in a lookup, or selecting cells for inclusion in SUMIF based on the text in a related cell. You can't use them to define a cell address or generally for an expression that needs to produce a specific value. An expression is like a formula. If you want to define a criterion of say "anything larger than 5", you can use">5". Since that includes a symbol, it needs to be in quotes. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
If the criterion was "anything equal to 5", you would have a choice of"=5"or just5(you can just specify the numeric value). A text string is a collection of characters that are to be treated only as text; nothing in it has a meaning that Excel is supposed to act on.
– fixer1234
Dec 18 '18 at 20:38
"Can you add a pseudo-code example?" --MAX(<cell_to_run_the_criterion_on>,<something else>). How do I specify<cell_to_run_the_criterion_on>?
– ivan_pozdeev
Dec 18 '18 at 20:46
|
show 3 more comments
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%2f1385441%2fexcel-sumif-criteria-reference%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
The complete specification for the SUMIF criteria, including syntax, appears to be:
- A numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2018, TRUE) or
- A cell reference or
- A text string (e.g. “Text”, “Thursday”) or
- A function (e.g.,
TODAY()) or - An expression (e.g. “>12”, “<>0”).
- Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is a numeric value, double quotation marks are not required.
- You can use the wildcard characters--the question mark (?) and asterisk (*)--in the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
That appears to be the entire, comprehensive description. Some sources are a little more descriptive than others for specific items. I compared numerous sources against the Microsoft documentation. This contains what looks like the most descriptive versions of the components, which came mostly from Microsoft Support and Corporate Finance Institute. The Microsoft Support link does contain everything, including the wildcard information.
Regarding which versions of Excel this applies to, I don't believe any of this has changed, at least in any version likely to still be in use (i.e., going back at least to v2007).
There are some things you can't do with SUMIF. They aren't included in the list of what you can do, but just for completeness:
- SUMIF is not one of the Excel functions for which the criteria can use array constants.
- You cannot use SUMIF with logical Boolean operators, such as OR and AND.
- When you want to use multiple conditions, array constants, nested IF statements or Boolean operators; or you want one total for multiple conditions (criteria); use the two functions SUM and IF together. For example, use SUM(IF()) instead of the SUMIF() function.
See Microsoft Support
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
So, how do I use an expression where the value shall go somewhere in the middle or into multiple places? Are wildcards applicable anywhere except plain text (e.g. in cell addresses, parts of an expression to evaluate)? How is plain text differentiated from an expression? <-- does anything cover these parts?
– ivan_pozdeev
Dec 18 '18 at 13:59
@ivan_pozdeev, Those things are not specific to SUMIF, they apply to anything you do in Excel, which is why they aren't described under SUMIF. The first item (value placement), it isn't clear what specific situation you're asking about. Can you add a pseudo-code example, or several if you're referring to a collection of cases? Basically, each use of SUMIF needs to follow the rules, and any component of a SUMIF needs to follow the rules, so I'm not understanding that part. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
Wildcards only ever apply to text strings, and you can use them only for making comparisons of text strings, like in a lookup, or selecting cells for inclusion in SUMIF based on the text in a related cell. You can't use them to define a cell address or generally for an expression that needs to produce a specific value. An expression is like a formula. If you want to define a criterion of say "anything larger than 5", you can use">5". Since that includes a symbol, it needs to be in quotes. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
If the criterion was "anything equal to 5", you would have a choice of"=5"or just5(you can just specify the numeric value). A text string is a collection of characters that are to be treated only as text; nothing in it has a meaning that Excel is supposed to act on.
– fixer1234
Dec 18 '18 at 20:38
"Can you add a pseudo-code example?" --MAX(<cell_to_run_the_criterion_on>,<something else>). How do I specify<cell_to_run_the_criterion_on>?
– ivan_pozdeev
Dec 18 '18 at 20:46
|
show 3 more comments
The complete specification for the SUMIF criteria, including syntax, appears to be:
- A numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2018, TRUE) or
- A cell reference or
- A text string (e.g. “Text”, “Thursday”) or
- A function (e.g.,
TODAY()) or - An expression (e.g. “>12”, “<>0”).
- Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is a numeric value, double quotation marks are not required.
- You can use the wildcard characters--the question mark (?) and asterisk (*)--in the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
That appears to be the entire, comprehensive description. Some sources are a little more descriptive than others for specific items. I compared numerous sources against the Microsoft documentation. This contains what looks like the most descriptive versions of the components, which came mostly from Microsoft Support and Corporate Finance Institute. The Microsoft Support link does contain everything, including the wildcard information.
Regarding which versions of Excel this applies to, I don't believe any of this has changed, at least in any version likely to still be in use (i.e., going back at least to v2007).
There are some things you can't do with SUMIF. They aren't included in the list of what you can do, but just for completeness:
- SUMIF is not one of the Excel functions for which the criteria can use array constants.
- You cannot use SUMIF with logical Boolean operators, such as OR and AND.
- When you want to use multiple conditions, array constants, nested IF statements or Boolean operators; or you want one total for multiple conditions (criteria); use the two functions SUM and IF together. For example, use SUM(IF()) instead of the SUMIF() function.
See Microsoft Support
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
So, how do I use an expression where the value shall go somewhere in the middle or into multiple places? Are wildcards applicable anywhere except plain text (e.g. in cell addresses, parts of an expression to evaluate)? How is plain text differentiated from an expression? <-- does anything cover these parts?
– ivan_pozdeev
Dec 18 '18 at 13:59
@ivan_pozdeev, Those things are not specific to SUMIF, they apply to anything you do in Excel, which is why they aren't described under SUMIF. The first item (value placement), it isn't clear what specific situation you're asking about. Can you add a pseudo-code example, or several if you're referring to a collection of cases? Basically, each use of SUMIF needs to follow the rules, and any component of a SUMIF needs to follow the rules, so I'm not understanding that part. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
Wildcards only ever apply to text strings, and you can use them only for making comparisons of text strings, like in a lookup, or selecting cells for inclusion in SUMIF based on the text in a related cell. You can't use them to define a cell address or generally for an expression that needs to produce a specific value. An expression is like a formula. If you want to define a criterion of say "anything larger than 5", you can use">5". Since that includes a symbol, it needs to be in quotes. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
If the criterion was "anything equal to 5", you would have a choice of"=5"or just5(you can just specify the numeric value). A text string is a collection of characters that are to be treated only as text; nothing in it has a meaning that Excel is supposed to act on.
– fixer1234
Dec 18 '18 at 20:38
"Can you add a pseudo-code example?" --MAX(<cell_to_run_the_criterion_on>,<something else>). How do I specify<cell_to_run_the_criterion_on>?
– ivan_pozdeev
Dec 18 '18 at 20:46
|
show 3 more comments
The complete specification for the SUMIF criteria, including syntax, appears to be:
- A numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2018, TRUE) or
- A cell reference or
- A text string (e.g. “Text”, “Thursday”) or
- A function (e.g.,
TODAY()) or - An expression (e.g. “>12”, “<>0”).
- Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is a numeric value, double quotation marks are not required.
- You can use the wildcard characters--the question mark (?) and asterisk (*)--in the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
That appears to be the entire, comprehensive description. Some sources are a little more descriptive than others for specific items. I compared numerous sources against the Microsoft documentation. This contains what looks like the most descriptive versions of the components, which came mostly from Microsoft Support and Corporate Finance Institute. The Microsoft Support link does contain everything, including the wildcard information.
Regarding which versions of Excel this applies to, I don't believe any of this has changed, at least in any version likely to still be in use (i.e., going back at least to v2007).
There are some things you can't do with SUMIF. They aren't included in the list of what you can do, but just for completeness:
- SUMIF is not one of the Excel functions for which the criteria can use array constants.
- You cannot use SUMIF with logical Boolean operators, such as OR and AND.
- When you want to use multiple conditions, array constants, nested IF statements or Boolean operators; or you want one total for multiple conditions (criteria); use the two functions SUM and IF together. For example, use SUM(IF()) instead of the SUMIF() function.
See Microsoft Support
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
The complete specification for the SUMIF criteria, including syntax, appears to be:
- A numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2018, TRUE) or
- A cell reference or
- A text string (e.g. “Text”, “Thursday”) or
- A function (e.g.,
TODAY()) or - An expression (e.g. “>12”, “<>0”).
- Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is a numeric value, double quotation marks are not required.
- You can use the wildcard characters--the question mark (?) and asterisk (*)--in the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
That appears to be the entire, comprehensive description. Some sources are a little more descriptive than others for specific items. I compared numerous sources against the Microsoft documentation. This contains what looks like the most descriptive versions of the components, which came mostly from Microsoft Support and Corporate Finance Institute. The Microsoft Support link does contain everything, including the wildcard information.
Regarding which versions of Excel this applies to, I don't believe any of this has changed, at least in any version likely to still be in use (i.e., going back at least to v2007).
There are some things you can't do with SUMIF. They aren't included in the list of what you can do, but just for completeness:
- SUMIF is not one of the Excel functions for which the criteria can use array constants.
- You cannot use SUMIF with logical Boolean operators, such as OR and AND.
- When you want to use multiple conditions, array constants, nested IF statements or Boolean operators; or you want one total for multiple conditions (criteria); use the two functions SUM and IF together. For example, use SUM(IF()) instead of the SUMIF() function.
See Microsoft Support
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
edited Dec 18 '18 at 12:19
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
answered Dec 18 '18 at 10:10
fixer1234
17.8k144581
17.8k144581
So, how do I use an expression where the value shall go somewhere in the middle or into multiple places? Are wildcards applicable anywhere except plain text (e.g. in cell addresses, parts of an expression to evaluate)? How is plain text differentiated from an expression? <-- does anything cover these parts?
– ivan_pozdeev
Dec 18 '18 at 13:59
@ivan_pozdeev, Those things are not specific to SUMIF, they apply to anything you do in Excel, which is why they aren't described under SUMIF. The first item (value placement), it isn't clear what specific situation you're asking about. Can you add a pseudo-code example, or several if you're referring to a collection of cases? Basically, each use of SUMIF needs to follow the rules, and any component of a SUMIF needs to follow the rules, so I'm not understanding that part. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
Wildcards only ever apply to text strings, and you can use them only for making comparisons of text strings, like in a lookup, or selecting cells for inclusion in SUMIF based on the text in a related cell. You can't use them to define a cell address or generally for an expression that needs to produce a specific value. An expression is like a formula. If you want to define a criterion of say "anything larger than 5", you can use">5". Since that includes a symbol, it needs to be in quotes. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
If the criterion was "anything equal to 5", you would have a choice of"=5"or just5(you can just specify the numeric value). A text string is a collection of characters that are to be treated only as text; nothing in it has a meaning that Excel is supposed to act on.
– fixer1234
Dec 18 '18 at 20:38
"Can you add a pseudo-code example?" --MAX(<cell_to_run_the_criterion_on>,<something else>). How do I specify<cell_to_run_the_criterion_on>?
– ivan_pozdeev
Dec 18 '18 at 20:46
|
show 3 more comments
So, how do I use an expression where the value shall go somewhere in the middle or into multiple places? Are wildcards applicable anywhere except plain text (e.g. in cell addresses, parts of an expression to evaluate)? How is plain text differentiated from an expression? <-- does anything cover these parts?
– ivan_pozdeev
Dec 18 '18 at 13:59
@ivan_pozdeev, Those things are not specific to SUMIF, they apply to anything you do in Excel, which is why they aren't described under SUMIF. The first item (value placement), it isn't clear what specific situation you're asking about. Can you add a pseudo-code example, or several if you're referring to a collection of cases? Basically, each use of SUMIF needs to follow the rules, and any component of a SUMIF needs to follow the rules, so I'm not understanding that part. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
Wildcards only ever apply to text strings, and you can use them only for making comparisons of text strings, like in a lookup, or selecting cells for inclusion in SUMIF based on the text in a related cell. You can't use them to define a cell address or generally for an expression that needs to produce a specific value. An expression is like a formula. If you want to define a criterion of say "anything larger than 5", you can use">5". Since that includes a symbol, it needs to be in quotes. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
If the criterion was "anything equal to 5", you would have a choice of"=5"or just5(you can just specify the numeric value). A text string is a collection of characters that are to be treated only as text; nothing in it has a meaning that Excel is supposed to act on.
– fixer1234
Dec 18 '18 at 20:38
"Can you add a pseudo-code example?" --MAX(<cell_to_run_the_criterion_on>,<something else>). How do I specify<cell_to_run_the_criterion_on>?
– ivan_pozdeev
Dec 18 '18 at 20:46
So, how do I use an expression where the value shall go somewhere in the middle or into multiple places? Are wildcards applicable anywhere except plain text (e.g. in cell addresses, parts of an expression to evaluate)? How is plain text differentiated from an expression? <-- does anything cover these parts?
– ivan_pozdeev
Dec 18 '18 at 13:59
So, how do I use an expression where the value shall go somewhere in the middle or into multiple places? Are wildcards applicable anywhere except plain text (e.g. in cell addresses, parts of an expression to evaluate)? How is plain text differentiated from an expression? <-- does anything cover these parts?
– ivan_pozdeev
Dec 18 '18 at 13:59
@ivan_pozdeev, Those things are not specific to SUMIF, they apply to anything you do in Excel, which is why they aren't described under SUMIF. The first item (value placement), it isn't clear what specific situation you're asking about. Can you add a pseudo-code example, or several if you're referring to a collection of cases? Basically, each use of SUMIF needs to follow the rules, and any component of a SUMIF needs to follow the rules, so I'm not understanding that part. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
@ivan_pozdeev, Those things are not specific to SUMIF, they apply to anything you do in Excel, which is why they aren't described under SUMIF. The first item (value placement), it isn't clear what specific situation you're asking about. Can you add a pseudo-code example, or several if you're referring to a collection of cases? Basically, each use of SUMIF needs to follow the rules, and any component of a SUMIF needs to follow the rules, so I'm not understanding that part. (cont'd)
– fixer1234
Dec 18 '18 at 20:38
Wildcards only ever apply to text strings, and you can use them only for making comparisons of text strings, like in a lookup, or selecting cells for inclusion in SUMIF based on the text in a related cell. You can't use them to define a cell address or generally for an expression that needs to produce a specific value. An expression is like a formula. If you want to define a criterion of say "anything larger than 5", you can use
">5". Since that includes a symbol, it needs to be in quotes. (cont'd)– fixer1234
Dec 18 '18 at 20:38
Wildcards only ever apply to text strings, and you can use them only for making comparisons of text strings, like in a lookup, or selecting cells for inclusion in SUMIF based on the text in a related cell. You can't use them to define a cell address or generally for an expression that needs to produce a specific value. An expression is like a formula. If you want to define a criterion of say "anything larger than 5", you can use
">5". Since that includes a symbol, it needs to be in quotes. (cont'd)– fixer1234
Dec 18 '18 at 20:38
If the criterion was "anything equal to 5", you would have a choice of
"=5" or just 5 (you can just specify the numeric value). A text string is a collection of characters that are to be treated only as text; nothing in it has a meaning that Excel is supposed to act on.– fixer1234
Dec 18 '18 at 20:38
If the criterion was "anything equal to 5", you would have a choice of
"=5" or just 5 (you can just specify the numeric value). A text string is a collection of characters that are to be treated only as text; nothing in it has a meaning that Excel is supposed to act on.– fixer1234
Dec 18 '18 at 20:38
"Can you add a pseudo-code example?" --
MAX(<cell_to_run_the_criterion_on>,<something else>). How do I specify <cell_to_run_the_criterion_on>?– ivan_pozdeev
Dec 18 '18 at 20:46
"Can you add a pseudo-code example?" --
MAX(<cell_to_run_the_criterion_on>,<something else>). How do I specify <cell_to_run_the_criterion_on>?– ivan_pozdeev
Dec 18 '18 at 20:46
|
show 3 more comments
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1385441%2fexcel-sumif-criteria-reference%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
SUMIF is basically Conditional SUM and to execute the Formula a proper combination using Logical & Relational Operates is required, Wild Card makes it more flexible with Criteria, ☺
– Rajesh S
Dec 18 '18 at 7:40
@fixer1234 I'm asking specifically about official documentation or other official sources. As I explained, random "learning materials" are not good enough for my needs because they can't guarantee accuracy and completeness.
– ivan_pozdeev
Dec 18 '18 at 8:41
"There isn't really a more comprehensive spec because that covers everything." I gave two cases that it doesn't cover. Who knows how many more there are.
– ivan_pozdeev
Dec 18 '18 at 9:58
Your question seems to stem from there appearing to be different partial information at different sources, including details at 3rd party sites that you didn't see at the Microsoft site. Actually, all of that was at the Microsoft site. Also, the relevant syntax rules are fully explained. Examples are available at your linked sites. That's the complete, official description. I posted an answer that added a little more explanation. Basically, you can do anything that falls within that description, and the syntax isn't complex. Beyond that, it isn't clear what you think isn't clear. (cont'd)
– fixer1234
Dec 18 '18 at 11:55
If you think you might still be missing some options, that's all there is. If you're unsure how to implement a specific example, including something you think isn't covered by the definition, that would make a good question and be something specific that people can respond to.
– fixer1234
Dec 18 '18 at 11:55