How to add multiple SUMIFS in Excel VBAs Worksheet function
I'm trying to create a formala that inside VBA worksheet functions that is the same or similar to the following inside of excel.
"=sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT1")+sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT2")"
I currently have
myanswer = Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")
if I add more to it such as
Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")+***Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2"***)
It gives an error (I have highlighted between *** the part of the code that stops working. I'm sure there is a simple "and" or "or" function but my brain is frazzled right now. Please let me know if you need any clarification (also if there are any typos I had to translate the code over as it's on another computer.)
excel vba excel-vba sumifs
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
add a comment |
I'm trying to create a formala that inside VBA worksheet functions that is the same or similar to the following inside of excel.
"=sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT1")+sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT2")"
I currently have
myanswer = Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")
if I add more to it such as
Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")+***Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2"***)
It gives an error (I have highlighted between *** the part of the code that stops working. I'm sure there is a simple "and" or "or" function but my brain is frazzled right now. Please let me know if you need any clarification (also if there are any typos I had to translate the code over as it's on another computer.)
excel vba excel-vba sumifs
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
add a comment |
I'm trying to create a formala that inside VBA worksheet functions that is the same or similar to the following inside of excel.
"=sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT1")+sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT2")"
I currently have
myanswer = Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")
if I add more to it such as
Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")+***Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2"***)
It gives an error (I have highlighted between *** the part of the code that stops working. I'm sure there is a simple "and" or "or" function but my brain is frazzled right now. Please let me know if you need any clarification (also if there are any typos I had to translate the code over as it's on another computer.)
excel vba excel-vba sumifs
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
I'm trying to create a formala that inside VBA worksheet functions that is the same or similar to the following inside of excel.
"=sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT1")+sumifs(U:U,O:O,"TEXT",Q:Q,"TEXT2")"
I currently have
myanswer = Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")
if I add more to it such as
Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1")+***Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2"***)
It gives an error (I have highlighted between *** the part of the code that stops working. I'm sure there is a simple "and" or "or" function but my brain is frazzled right now. Please let me know if you need any clarification (also if there are any typos I had to translate the code over as it's on another computer.)
excel vba excel-vba sumifs
excel vba excel-vba sumifs
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
edited Nov 22 '18 at 7:12
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
asked Nov 21 '18 at 18:38
J. KhostJ. Khost
83
83
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This works for me. I've only posted as an answer to show the formatting, including the line break (not required but makes code easier to read)
MsgBox Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1") _
+ Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2")
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
1
Thank you so much, I'm not sure why my code wasn't working. It just wanted me to ask the question to start work. I probably had a typo in my original code but either way. Thank you very much.
– J. Khost
Nov 21 '18 at 18:54
OK, glad it worked.
– SJR
Nov 21 '18 at 18:56
add a comment |
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%2f53418585%2fhow-to-add-multiple-sumifs-in-excel-vbas-worksheet-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
This works for me. I've only posted as an answer to show the formatting, including the line break (not required but makes code easier to read)
MsgBox Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1") _
+ Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2")
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
1
Thank you so much, I'm not sure why my code wasn't working. It just wanted me to ask the question to start work. I probably had a typo in my original code but either way. Thank you very much.
– J. Khost
Nov 21 '18 at 18:54
OK, glad it worked.
– SJR
Nov 21 '18 at 18:56
add a comment |
This works for me. I've only posted as an answer to show the formatting, including the line break (not required but makes code easier to read)
MsgBox Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1") _
+ Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2")
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
1
Thank you so much, I'm not sure why my code wasn't working. It just wanted me to ask the question to start work. I probably had a typo in my original code but either way. Thank you very much.
– J. Khost
Nov 21 '18 at 18:54
OK, glad it worked.
– SJR
Nov 21 '18 at 18:56
add a comment |
This works for me. I've only posted as an answer to show the formatting, including the line break (not required but makes code easier to read)
MsgBox Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1") _
+ Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2")
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
This works for me. I've only posted as an answer to show the formatting, including the line break (not required but makes code easier to read)
MsgBox Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT1") _
+ Application.WorksheetFunction.SumIfs(Range("U:U"), Range("O:O"), "TEXT", Range("Q:Q"), "TEXT2")
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
answered Nov 21 '18 at 18:46
SJRSJR
11.6k31117
11.6k31117
1
Thank you so much, I'm not sure why my code wasn't working. It just wanted me to ask the question to start work. I probably had a typo in my original code but either way. Thank you very much.
– J. Khost
Nov 21 '18 at 18:54
OK, glad it worked.
– SJR
Nov 21 '18 at 18:56
add a comment |
1
Thank you so much, I'm not sure why my code wasn't working. It just wanted me to ask the question to start work. I probably had a typo in my original code but either way. Thank you very much.
– J. Khost
Nov 21 '18 at 18:54
OK, glad it worked.
– SJR
Nov 21 '18 at 18:56
1
1
Thank you so much, I'm not sure why my code wasn't working. It just wanted me to ask the question to start work. I probably had a typo in my original code but either way. Thank you very much.
– J. Khost
Nov 21 '18 at 18:54
Thank you so much, I'm not sure why my code wasn't working. It just wanted me to ask the question to start work. I probably had a typo in my original code but either way. Thank you very much.
– J. Khost
Nov 21 '18 at 18:54
OK, glad it worked.
– SJR
Nov 21 '18 at 18:56
OK, glad it worked.
– SJR
Nov 21 '18 at 18:56
add a comment |
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.
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%2fstackoverflow.com%2fquestions%2f53418585%2fhow-to-add-multiple-sumifs-in-excel-vbas-worksheet-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
