sumifs syntax for multiple conditions incorrect
I'm currently working from two worksheets, a main data sheet titled 'Equities' which details the stock trading data undertaken on a daily basis. I've attached a screenshot of the column labels below for this worksheet.
I have a monthly commission worksheet which draws information from the equities sheet using sumifs to return results to a relevant date range. I've attached the column labels screenshot below and the date column to show how it is categorised.
=SUMIFS(Equities!L:L,Equities!A:A,">=1/10/2018",Equities!A:A,"<=31/10/2018")
The above sumif returns an accurate result for the gross revenue USD column on the monthly commission worksheet. I have tried to adjust the sumif by adding an additional criteria so as to have monthly figures for Trader 1, 2, 3 etc.
=SUMIFS(Equities!$N:$N, Equities!$L:$L, Monthly Commission!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018"
The SUMIF above is the one I've edited to include the trader as an additional criteria. However, it has not worked and returns a 0 value, and also prompts me to open a file. I'm not sure where the error is in the syntax or composition of the sumif, so any advice on how to correct it would be greatly appreciated.
excel function sumifs
asked Nov 14 '18 at 11:11
NHure92NHure92
406
|
show 1 more comment
I'm currently working from two worksheets, a main data sheet titled 'Equities' which details the stock trading data undertaken on a daily basis. I've attached a screenshot of the column labels below for this worksheet.
I have a monthly commission worksheet which draws information from the equities sheet using sumifs to return results to a relevant date range. I've attached the column labels screenshot below and the date column to show how it is categorised.
=SUMIFS(Equities!L:L,Equities!A:A,">=1/10/2018",Equities!A:A,"<=31/10/2018")
The above sumif returns an accurate result for the gross revenue USD column on the monthly commission worksheet. I have tried to adjust the sumif by adding an additional criteria so as to have monthly figures for Trader 1, 2, 3 etc.
=SUMIFS(Equities!$N:$N, Equities!$L:$L, Monthly Commission!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018"
The SUMIF above is the one I've edited to include the trader as an additional criteria. However, it has not worked and returns a 0 value, and also prompts me to open a file. I'm not sure where the error is in the syntax or composition of the sumif, so any advice on how to correct it would be greatly appreciated.
excel function sumifs
asked Nov 14 '18 at 11:11
NHure92NHure92
406
The criterion"<=31/10/2018"compares the cell's value to the numerical value (31/10)/2018, or 0.001536. Instead, try"<="&DATE(2018,10,31).
– jsheeran
Nov 14 '18 at 11:58
1
In your first formula you are summing Equities!$L:$L. In your second formula you are summing Equities!$N:$N, and this time Equities!$L:$L has switched from being the column to sum to being one of the columns subject to a criterion (=Monthly Commission!$C1). Is this what you intended?
– XOR LX
Nov 14 '18 at 13:23
hi @jsheeran I have tried this but it returns #VALUE! thank you for the suggestion, do you know what might be causing that?
– NHure92
Nov 14 '18 at 13:23
Hi @XORLX, and thank you so much for the assistance in my previous question. To answer, I believe column L (which has the gross comm figures) will need to be summed as it was previously. Column N which has the traders name should be a column subject to the additional criterion in C1$
– NHure92
Nov 14 '18 at 13:27
2
So just switch the order of those two columns in the second formula you posted, as currently they are the wrong way round (the FIRST column referenced in SUMIFS is ALWAYS that to be summed - for the record, an annoyingly different syntax to that for SUMIF)
– XOR LX
Nov 14 '18 at 13:28
|
show 1 more comment
I'm currently working from two worksheets, a main data sheet titled 'Equities' which details the stock trading data undertaken on a daily basis. I've attached a screenshot of the column labels below for this worksheet.
I have a monthly commission worksheet which draws information from the equities sheet using sumifs to return results to a relevant date range. I've attached the column labels screenshot below and the date column to show how it is categorised.
=SUMIFS(Equities!L:L,Equities!A:A,">=1/10/2018",Equities!A:A,"<=31/10/2018")
The above sumif returns an accurate result for the gross revenue USD column on the monthly commission worksheet. I have tried to adjust the sumif by adding an additional criteria so as to have monthly figures for Trader 1, 2, 3 etc.
=SUMIFS(Equities!$N:$N, Equities!$L:$L, Monthly Commission!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018"
The SUMIF above is the one I've edited to include the trader as an additional criteria. However, it has not worked and returns a 0 value, and also prompts me to open a file. I'm not sure where the error is in the syntax or composition of the sumif, so any advice on how to correct it would be greatly appreciated.
excel function sumifs
asked Nov 14 '18 at 11:11
NHure92NHure92
406
I'm currently working from two worksheets, a main data sheet titled 'Equities' which details the stock trading data undertaken on a daily basis. I've attached a screenshot of the column labels below for this worksheet.
I have a monthly commission worksheet which draws information from the equities sheet using sumifs to return results to a relevant date range. I've attached the column labels screenshot below and the date column to show how it is categorised.
=SUMIFS(Equities!L:L,Equities!A:A,">=1/10/2018",Equities!A:A,"<=31/10/2018")
The above sumif returns an accurate result for the gross revenue USD column on the monthly commission worksheet. I have tried to adjust the sumif by adding an additional criteria so as to have monthly figures for Trader 1, 2, 3 etc.
=SUMIFS(Equities!$N:$N, Equities!$L:$L, Monthly Commission!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018"
The SUMIF above is the one I've edited to include the trader as an additional criteria. However, it has not worked and returns a 0 value, and also prompts me to open a file. I'm not sure where the error is in the syntax or composition of the sumif, so any advice on how to correct it would be greatly appreciated.
excel function sumifs
excel function sumifs
asked Nov 14 '18 at 11:11
NHure92NHure92
406
asked Nov 14 '18 at 11:11
NHure92NHure92
406
asked Nov 14 '18 at 11:11
NHure92NHure92
406
asked Nov 14 '18 at 11:11
NHure92NHure92
406
asked Nov 14 '18 at 11:11
NHure92NHure92
406
406
The criterion"<=31/10/2018"compares the cell's value to the numerical value (31/10)/2018, or 0.001536. Instead, try"<="&DATE(2018,10,31).
– jsheeran
Nov 14 '18 at 11:58
1
In your first formula you are summing Equities!$L:$L. In your second formula you are summing Equities!$N:$N, and this time Equities!$L:$L has switched from being the column to sum to being one of the columns subject to a criterion (=Monthly Commission!$C1). Is this what you intended?
– XOR LX
Nov 14 '18 at 13:23
hi @jsheeran I have tried this but it returns #VALUE! thank you for the suggestion, do you know what might be causing that?
– NHure92
Nov 14 '18 at 13:23
Hi @XORLX, and thank you so much for the assistance in my previous question. To answer, I believe column L (which has the gross comm figures) will need to be summed as it was previously. Column N which has the traders name should be a column subject to the additional criterion in C1$
– NHure92
Nov 14 '18 at 13:27
2
So just switch the order of those two columns in the second formula you posted, as currently they are the wrong way round (the FIRST column referenced in SUMIFS is ALWAYS that to be summed - for the record, an annoyingly different syntax to that for SUMIF)
– XOR LX
Nov 14 '18 at 13:28
|
show 1 more comment
The criterion"<=31/10/2018"compares the cell's value to the numerical value (31/10)/2018, or 0.001536. Instead, try"<="&DATE(2018,10,31).
– jsheeran
Nov 14 '18 at 11:58
1
In your first formula you are summing Equities!$L:$L. In your second formula you are summing Equities!$N:$N, and this time Equities!$L:$L has switched from being the column to sum to being one of the columns subject to a criterion (=Monthly Commission!$C1). Is this what you intended?
– XOR LX
Nov 14 '18 at 13:23
hi @jsheeran I have tried this but it returns #VALUE! thank you for the suggestion, do you know what might be causing that?
– NHure92
Nov 14 '18 at 13:23
Hi @XORLX, and thank you so much for the assistance in my previous question. To answer, I believe column L (which has the gross comm figures) will need to be summed as it was previously. Column N which has the traders name should be a column subject to the additional criterion in C1$
– NHure92
Nov 14 '18 at 13:27
2
So just switch the order of those two columns in the second formula you posted, as currently they are the wrong way round (the FIRST column referenced in SUMIFS is ALWAYS that to be summed - for the record, an annoyingly different syntax to that for SUMIF)
– XOR LX
Nov 14 '18 at 13:28
The criterion
"<=31/10/2018" compares the cell's value to the numerical value (31/10)/2018, or 0.001536. Instead, try "<="&DATE(2018,10,31).– jsheeran
Nov 14 '18 at 11:58
The criterion
"<=31/10/2018" compares the cell's value to the numerical value (31/10)/2018, or 0.001536. Instead, try "<="&DATE(2018,10,31).– jsheeran
Nov 14 '18 at 11:58
1
1
In your first formula you are summing Equities!$L:$L. In your second formula you are summing Equities!$N:$N, and this time Equities!$L:$L has switched from being the column to sum to being one of the columns subject to a criterion (=Monthly Commission!$C1). Is this what you intended?
– XOR LX
Nov 14 '18 at 13:23
In your first formula you are summing Equities!$L:$L. In your second formula you are summing Equities!$N:$N, and this time Equities!$L:$L has switched from being the column to sum to being one of the columns subject to a criterion (=Monthly Commission!$C1). Is this what you intended?
– XOR LX
Nov 14 '18 at 13:23
hi @jsheeran I have tried this but it returns #VALUE! thank you for the suggestion, do you know what might be causing that?
– NHure92
Nov 14 '18 at 13:23
hi @jsheeran I have tried this but it returns #VALUE! thank you for the suggestion, do you know what might be causing that?
– NHure92
Nov 14 '18 at 13:23
Hi @XORLX, and thank you so much for the assistance in my previous question. To answer, I believe column L (which has the gross comm figures) will need to be summed as it was previously. Column N which has the traders name should be a column subject to the additional criterion in C1$
– NHure92
Nov 14 '18 at 13:27
Hi @XORLX, and thank you so much for the assistance in my previous question. To answer, I believe column L (which has the gross comm figures) will need to be summed as it was previously. Column N which has the traders name should be a column subject to the additional criterion in C1$
– NHure92
Nov 14 '18 at 13:27
2
2
So just switch the order of those two columns in the second formula you posted, as currently they are the wrong way round (the FIRST column referenced in SUMIFS is ALWAYS that to be summed - for the record, an annoyingly different syntax to that for SUMIF)
– XOR LX
Nov 14 '18 at 13:28
So just switch the order of those two columns in the second formula you posted, as currently they are the wrong way round (the FIRST column referenced in SUMIFS is ALWAYS that to be summed - for the record, an annoyingly different syntax to that for SUMIF)
– XOR LX
Nov 14 '18 at 13:28
|
show 1 more comment
2 Answers
2
active
oldest
votes
Because the name of the Monthly Commission sheet has a space in it, you need to put it in single quotes:
=SUMIFS(Equities!$N:$N, Equities!$L:$L, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
Thanks for the reply Tom. I've tried this and while it doesn't prompt me to open a file or give me an error, it only gives me an answer of 0. Do you know why this may be?
– NHure92
Nov 14 '18 at 12:34
I've put some simple data in to test it and it works OK for me (i.e. gives a non-zero result). Your dates look as if they are formatted OK (do they appear as 1/10/2018 etc. in the formula box?) , and I have checked @jsheeran's comment, so I don't know - sorry!
– Tom Sharpe
Nov 14 '18 at 13:04
The comments from @XOR LX etc. look helpful - I think they probably explain why you are getting a zero result.
– Tom Sharpe
Nov 14 '18 at 13:46
add a comment |
The incorrect column was being summed, answer provided by @XORLX.
=SUMIFS(Equities!$L:$L, Equities!$N:$N, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 14:09
NHure92NHure92
406
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%2f53298856%2fsumifs-syntax-for-multiple-conditions-incorrect%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Because the name of the Monthly Commission sheet has a space in it, you need to put it in single quotes:
=SUMIFS(Equities!$N:$N, Equities!$L:$L, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
Thanks for the reply Tom. I've tried this and while it doesn't prompt me to open a file or give me an error, it only gives me an answer of 0. Do you know why this may be?
– NHure92
Nov 14 '18 at 12:34
I've put some simple data in to test it and it works OK for me (i.e. gives a non-zero result). Your dates look as if they are formatted OK (do they appear as 1/10/2018 etc. in the formula box?) , and I have checked @jsheeran's comment, so I don't know - sorry!
– Tom Sharpe
Nov 14 '18 at 13:04
The comments from @XOR LX etc. look helpful - I think they probably explain why you are getting a zero result.
– Tom Sharpe
Nov 14 '18 at 13:46
add a comment |
Because the name of the Monthly Commission sheet has a space in it, you need to put it in single quotes:
=SUMIFS(Equities!$N:$N, Equities!$L:$L, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
Thanks for the reply Tom. I've tried this and while it doesn't prompt me to open a file or give me an error, it only gives me an answer of 0. Do you know why this may be?
– NHure92
Nov 14 '18 at 12:34
I've put some simple data in to test it and it works OK for me (i.e. gives a non-zero result). Your dates look as if they are formatted OK (do they appear as 1/10/2018 etc. in the formula box?) , and I have checked @jsheeran's comment, so I don't know - sorry!
– Tom Sharpe
Nov 14 '18 at 13:04
The comments from @XOR LX etc. look helpful - I think they probably explain why you are getting a zero result.
– Tom Sharpe
Nov 14 '18 at 13:46
add a comment |
Because the name of the Monthly Commission sheet has a space in it, you need to put it in single quotes:
=SUMIFS(Equities!$N:$N, Equities!$L:$L, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
Because the name of the Monthly Commission sheet has a space in it, you need to put it in single quotes:
=SUMIFS(Equities!$N:$N, Equities!$L:$L, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
edited Nov 14 '18 at 12:33
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
answered Nov 14 '18 at 11:53
Tom SharpeTom Sharpe
12.6k31224
12.6k31224
Thanks for the reply Tom. I've tried this and while it doesn't prompt me to open a file or give me an error, it only gives me an answer of 0. Do you know why this may be?
– NHure92
Nov 14 '18 at 12:34
I've put some simple data in to test it and it works OK for me (i.e. gives a non-zero result). Your dates look as if they are formatted OK (do they appear as 1/10/2018 etc. in the formula box?) , and I have checked @jsheeran's comment, so I don't know - sorry!
– Tom Sharpe
Nov 14 '18 at 13:04
The comments from @XOR LX etc. look helpful - I think they probably explain why you are getting a zero result.
– Tom Sharpe
Nov 14 '18 at 13:46
add a comment |
Thanks for the reply Tom. I've tried this and while it doesn't prompt me to open a file or give me an error, it only gives me an answer of 0. Do you know why this may be?
– NHure92
Nov 14 '18 at 12:34
I've put some simple data in to test it and it works OK for me (i.e. gives a non-zero result). Your dates look as if they are formatted OK (do they appear as 1/10/2018 etc. in the formula box?) , and I have checked @jsheeran's comment, so I don't know - sorry!
– Tom Sharpe
Nov 14 '18 at 13:04
The comments from @XOR LX etc. look helpful - I think they probably explain why you are getting a zero result.
– Tom Sharpe
Nov 14 '18 at 13:46
Thanks for the reply Tom. I've tried this and while it doesn't prompt me to open a file or give me an error, it only gives me an answer of 0. Do you know why this may be?
– NHure92
Nov 14 '18 at 12:34
Thanks for the reply Tom. I've tried this and while it doesn't prompt me to open a file or give me an error, it only gives me an answer of 0. Do you know why this may be?
– NHure92
Nov 14 '18 at 12:34
I've put some simple data in to test it and it works OK for me (i.e. gives a non-zero result). Your dates look as if they are formatted OK (do they appear as 1/10/2018 etc. in the formula box?) , and I have checked @jsheeran's comment, so I don't know - sorry!
– Tom Sharpe
Nov 14 '18 at 13:04
I've put some simple data in to test it and it works OK for me (i.e. gives a non-zero result). Your dates look as if they are formatted OK (do they appear as 1/10/2018 etc. in the formula box?) , and I have checked @jsheeran's comment, so I don't know - sorry!
– Tom Sharpe
Nov 14 '18 at 13:04
The comments from @XOR LX etc. look helpful - I think they probably explain why you are getting a zero result.
– Tom Sharpe
Nov 14 '18 at 13:46
The comments from @XOR LX etc. look helpful - I think they probably explain why you are getting a zero result.
– Tom Sharpe
Nov 14 '18 at 13:46
add a comment |
The incorrect column was being summed, answer provided by @XORLX.
=SUMIFS(Equities!$L:$L, Equities!$N:$N, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 14:09
NHure92NHure92
406
add a comment |
The incorrect column was being summed, answer provided by @XORLX.
=SUMIFS(Equities!$L:$L, Equities!$N:$N, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 14:09
NHure92NHure92
406
add a comment |
The incorrect column was being summed, answer provided by @XORLX.
=SUMIFS(Equities!$L:$L, Equities!$N:$N, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 14:09
NHure92NHure92
406
The incorrect column was being summed, answer provided by @XORLX.
=SUMIFS(Equities!$L:$L, Equities!$N:$N, 'Monthly Commission'!$C1, Equities!$A:$A,">=1/10/2018",Equities!$A:$A,"<=31/10/2018")
answered Nov 14 '18 at 14:09
NHure92NHure92
406
answered Nov 14 '18 at 14:09
NHure92NHure92
406
answered Nov 14 '18 at 14:09
NHure92NHure92
406
answered Nov 14 '18 at 14:09
NHure92NHure92
406
406
add a comment |
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.
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%2f53298856%2fsumifs-syntax-for-multiple-conditions-incorrect%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
The criterion
"<=31/10/2018"compares the cell's value to the numerical value (31/10)/2018, or 0.001536. Instead, try"<="&DATE(2018,10,31).– jsheeran
Nov 14 '18 at 11:58
1
In your first formula you are summing Equities!$L:$L. In your second formula you are summing Equities!$N:$N, and this time Equities!$L:$L has switched from being the column to sum to being one of the columns subject to a criterion (=Monthly Commission!$C1). Is this what you intended?
– XOR LX
Nov 14 '18 at 13:23
hi @jsheeran I have tried this but it returns #VALUE! thank you for the suggestion, do you know what might be causing that?
– NHure92
Nov 14 '18 at 13:23
Hi @XORLX, and thank you so much for the assistance in my previous question. To answer, I believe column L (which has the gross comm figures) will need to be summed as it was previously. Column N which has the traders name should be a column subject to the additional criterion in C1$
– NHure92
Nov 14 '18 at 13:27
2
So just switch the order of those two columns in the second formula you posted, as currently they are the wrong way round (the FIRST column referenced in SUMIFS is ALWAYS that to be summed - for the record, an annoyingly different syntax to that for SUMIF)
– XOR LX
Nov 14 '18 at 13:28