Excel and use OR in a SUMIFS function Unicorn Meta Zoo #1: Why another podcast? ...
Why is arima in R one time step off?
Simulate round-robin tournament draw
`FindRoot [ ]`::jsing: Encountered a singular Jacobian at a point...WHY
Did war bonds have better investment alternatives during WWII?
In search of the origins of term censor, I hit a dead end stuck with the greek term, to censor, λογοκρίνω
"Working on a knee"
Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table
What is the evidence that custom checks in Northern Ireland are going to result in violence?
What is ls Largest Number Formed by only moving two sticks in 508?
Why do people think Winterfell crypts is the safest place for women, children & old people?
Is it OK if I do not take the receipt in Germany?
Determinant of a matrix with 2 equal rows
false 'Security alert' from Google - every login generates mails from 'no-reply@accounts.google.com'
When does Bran Stark remember Jamie pushing him?
Married in secret, can marital status in passport be changed at a later date?
Is there a way to fake a method response using Mock or Stubs?
What do you call an IPA symbol that lacks a name (e.g. ɲ)?
Why isn't everyone flabbergasted about Bran's "gift"?
Is a self contained air-bullet cartridge feasible?
How did Elite on the NES work?
Does Prince Arnaud cause someone holding the Princess to lose?
Bright yellow or light yellow?
Why doesn't the university give past final exams' answers?
What's the difference between using dependency injection with a container and using a service locator?
Excel and use OR in a SUMIFS function
Unicorn Meta Zoo #1: Why another podcast?
Announcing the arrival of Valued Associate #679: Cesar Manaraexcel function searching multiple rowsFunction excel for split a cell, vlookup into a column and concatenaExcel 2013, SUMIFS formulaExcel substitute function match entire cell contentsHow to define an alias for user function in Excel?Excel Compare Function / SoftwareSUMIFS and INDIRECT formulasExcel how to deactivate “Most Recently used function” suggestion from drop down listDisplay value based from multiple dropdown list with IF, AND, SUMIFSSUMIFS function with a criteria for substring match
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am using this function.
=IF(ISNUMBER(SEARCH("LOS",$A6))=TRUE,$C6,"")
However I really want to look for "LOS" OR "DTS". Can this be done with the SUMIFS Function? If yes, please show an example.
worksheet-function
edited 11 hours ago
Scott Craner
12.6k11318
asked 11 hours ago
JohnJohn
1
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am using this function.
=IF(ISNUMBER(SEARCH("LOS",$A6))=TRUE,$C6,"")
However I really want to look for "LOS" OR "DTS". Can this be done with the SUMIFS Function? If yes, please show an example.
worksheet-function
edited 11 hours ago
Scott Craner
12.6k11318
asked 11 hours ago
JohnJohn
1
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Sumifs is for a range of cells, you are testing only one. Do you want to do a range of Cells?
– Scott Craner
11 hours ago
add a comment |
I am using this function.
=IF(ISNUMBER(SEARCH("LOS",$A6))=TRUE,$C6,"")
However I really want to look for "LOS" OR "DTS". Can this be done with the SUMIFS Function? If yes, please show an example.
worksheet-function
edited 11 hours ago
Scott Craner
12.6k11318
asked 11 hours ago
JohnJohn
1
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I am using this function.
=IF(ISNUMBER(SEARCH("LOS",$A6))=TRUE,$C6,"")
However I really want to look for "LOS" OR "DTS". Can this be done with the SUMIFS Function? If yes, please show an example.
worksheet-function
worksheet-function
edited 11 hours ago
Scott Craner
12.6k11318
asked 11 hours ago
JohnJohn
1
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 11 hours ago
Scott Craner
12.6k11318
asked 11 hours ago
JohnJohn
1
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 11 hours ago
Scott Craner
12.6k11318
edited 11 hours ago
Scott Craner
12.6k11318
edited 11 hours ago
Scott Craner
12.6k11318
12.6k11318
asked 11 hours ago
JohnJohn
1
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 11 hours ago
JohnJohn
1
asked 11 hours ago
JohnJohn
1
1
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Sumifs is for a range of cells, you are testing only one. Do you want to do a range of Cells?
– Scott Craner
11 hours ago
add a comment |
Sumifs is for a range of cells, you are testing only one. Do you want to do a range of Cells?
– Scott Craner
11 hours ago
Sumifs is for a range of cells, you are testing only one. Do you want to do a range of Cells?
– Scott Craner
11 hours ago
Sumifs is for a range of cells, you are testing only one. Do you want to do a range of Cells?
– Scott Craner
11 hours ago
add a comment |
1 Answer
1
active
oldest
votes
If you are testing only one cell I would use COUNTIF with a SUMPRODUCT wrapper:
=IF(SUMPRODUCT(COUNTIF($A6,{"*LOS*","*DTS*"})),$C6,"")
This will effectively run two COUNTIF()s and sum their results. COUNTIF() allows the use of WildCards. IF either is found it will return a value of >0 and the IF will see that as True and return the value in C6.
If, in fact, you want to sum all of C:C where A:A has those two strings then yes a SUMIF will work:
=SUMPRODUCT(SUMIF(C:C,A:A,{"*LOS*","*DTS*"}))
Like the COUNTIF() this will do two SUMIF()s and return the sum of both. One will look for all the values in A that have LOS and the other DTS and return the sum of the matches.
If the cell has both LOS and DTS the value in C will be added twice.
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
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
});
}
});
John is a new contributor. Be nice, and check out our Code of Conduct.
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%2f1428729%2fexcel-and-use-or-in-a-sumifs-function%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
If you are testing only one cell I would use COUNTIF with a SUMPRODUCT wrapper:
=IF(SUMPRODUCT(COUNTIF($A6,{"*LOS*","*DTS*"})),$C6,"")
This will effectively run two COUNTIF()s and sum their results. COUNTIF() allows the use of WildCards. IF either is found it will return a value of >0 and the IF will see that as True and return the value in C6.
If, in fact, you want to sum all of C:C where A:A has those two strings then yes a SUMIF will work:
=SUMPRODUCT(SUMIF(C:C,A:A,{"*LOS*","*DTS*"}))
Like the COUNTIF() this will do two SUMIF()s and return the sum of both. One will look for all the values in A that have LOS and the other DTS and return the sum of the matches.
If the cell has both LOS and DTS the value in C will be added twice.
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
add a comment |
If you are testing only one cell I would use COUNTIF with a SUMPRODUCT wrapper:
=IF(SUMPRODUCT(COUNTIF($A6,{"*LOS*","*DTS*"})),$C6,"")
This will effectively run two COUNTIF()s and sum their results. COUNTIF() allows the use of WildCards. IF either is found it will return a value of >0 and the IF will see that as True and return the value in C6.
If, in fact, you want to sum all of C:C where A:A has those two strings then yes a SUMIF will work:
=SUMPRODUCT(SUMIF(C:C,A:A,{"*LOS*","*DTS*"}))
Like the COUNTIF() this will do two SUMIF()s and return the sum of both. One will look for all the values in A that have LOS and the other DTS and return the sum of the matches.
If the cell has both LOS and DTS the value in C will be added twice.
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
add a comment |
If you are testing only one cell I would use COUNTIF with a SUMPRODUCT wrapper:
=IF(SUMPRODUCT(COUNTIF($A6,{"*LOS*","*DTS*"})),$C6,"")
This will effectively run two COUNTIF()s and sum their results. COUNTIF() allows the use of WildCards. IF either is found it will return a value of >0 and the IF will see that as True and return the value in C6.
If, in fact, you want to sum all of C:C where A:A has those two strings then yes a SUMIF will work:
=SUMPRODUCT(SUMIF(C:C,A:A,{"*LOS*","*DTS*"}))
Like the COUNTIF() this will do two SUMIF()s and return the sum of both. One will look for all the values in A that have LOS and the other DTS and return the sum of the matches.
If the cell has both LOS and DTS the value in C will be added twice.
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
If you are testing only one cell I would use COUNTIF with a SUMPRODUCT wrapper:
=IF(SUMPRODUCT(COUNTIF($A6,{"*LOS*","*DTS*"})),$C6,"")
This will effectively run two COUNTIF()s and sum their results. COUNTIF() allows the use of WildCards. IF either is found it will return a value of >0 and the IF will see that as True and return the value in C6.
If, in fact, you want to sum all of C:C where A:A has those two strings then yes a SUMIF will work:
=SUMPRODUCT(SUMIF(C:C,A:A,{"*LOS*","*DTS*"}))
Like the COUNTIF() this will do two SUMIF()s and return the sum of both. One will look for all the values in A that have LOS and the other DTS and return the sum of the matches.
If the cell has both LOS and DTS the value in C will be added twice.
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
answered 11 hours ago
Scott CranerScott Craner
12.6k11318
12.6k11318
add a comment |
add a comment |
John is a new contributor. Be nice, and check out our Code of Conduct.
John is a new contributor. Be nice, and check out our Code of Conduct.
John is a new contributor. Be nice, and check out our Code of Conduct.
John is a new contributor. Be nice, and check out our Code of Conduct.
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%2f1428729%2fexcel-and-use-or-in-a-sumifs-function%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
Sumifs is for a range of cells, you are testing only one. Do you want to do a range of Cells?
– Scott Craner
11 hours ago