SUMIF INDEX MATCH
-3
I am trying to look up and sum up values on a another excel sheet where the sum up values are with certain criterias in a row.
Example below where:
criteria 1=date in Column D,
criteria 2= "SUB" or "RED" in column F.
I am not sure why my formula does not work at the sum stage:
excel excel-formula
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
|
show 1 more comment
-3
I am trying to look up and sum up values on a another excel sheet where the sum up values are with certain criterias in a row.
Example below where:
criteria 1=date in Column D,
criteria 2= "SUB" or "RED" in column F.
I am not sure why my formula does not work at the sum stage:
excel excel-formula
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
please clarify what the desired output is and how/why your current formula is not delivering the output you need Include a sample dataset and the formula(s) in your question
– Chrisvdberge
Nov 14 '18 at 12:57
I need to sum up values in the blue range by looking up data in the yellow range by criteria (date & type). In J12 I am expecting 452,317.76, because there are 2 values for the 01/01//018 in the yellow range, a SUB and a RED. (226,158.88 x 2). I do not know why my formula does not work.
– Bálint Ákos
Nov 14 '18 at 14:21
Also, I need to do it with INDEX MATCH because the underlying data is in another excel sheet and I cannot have it open all the time. with INDEX MATCH, you can have the underlying excel closed.
– Bálint Ákos
Nov 14 '18 at 14:28
not sure why you are checking on type, as your explanation of the expected output is just summing it by date (in disregards of type).
– Chrisvdberge
Nov 14 '18 at 14:48
btw, did you do Ctrl+Shft+Enter to make it an array formula?
– Chrisvdberge
Nov 14 '18 at 14:53
|
show 1 more comment
-3
-3
-3
I am trying to look up and sum up values on a another excel sheet where the sum up values are with certain criterias in a row.
Example below where:
criteria 1=date in Column D,
criteria 2= "SUB" or "RED" in column F.
I am not sure why my formula does not work at the sum stage:
excel excel-formula
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
I am trying to look up and sum up values on a another excel sheet where the sum up values are with certain criterias in a row.
Example below where:
criteria 1=date in Column D,
criteria 2= "SUB" or "RED" in column F.
I am not sure why my formula does not work at the sum stage:
excel excel-formula
excel excel-formula
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
edited Nov 14 '18 at 21:35
Simon Zyx
2,5221828
2,5221828
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
asked Nov 14 '18 at 12:19
Bálint ÁkosBálint Ákos
11
11
please clarify what the desired output is and how/why your current formula is not delivering the output you need Include a sample dataset and the formula(s) in your question
– Chrisvdberge
Nov 14 '18 at 12:57
I need to sum up values in the blue range by looking up data in the yellow range by criteria (date & type). In J12 I am expecting 452,317.76, because there are 2 values for the 01/01//018 in the yellow range, a SUB and a RED. (226,158.88 x 2). I do not know why my formula does not work.
– Bálint Ákos
Nov 14 '18 at 14:21
Also, I need to do it with INDEX MATCH because the underlying data is in another excel sheet and I cannot have it open all the time. with INDEX MATCH, you can have the underlying excel closed.
– Bálint Ákos
Nov 14 '18 at 14:28
not sure why you are checking on type, as your explanation of the expected output is just summing it by date (in disregards of type).
– Chrisvdberge
Nov 14 '18 at 14:48
btw, did you do Ctrl+Shft+Enter to make it an array formula?
– Chrisvdberge
Nov 14 '18 at 14:53
|
show 1 more comment
please clarify what the desired output is and how/why your current formula is not delivering the output you need Include a sample dataset and the formula(s) in your question
– Chrisvdberge
Nov 14 '18 at 12:57
I need to sum up values in the blue range by looking up data in the yellow range by criteria (date & type). In J12 I am expecting 452,317.76, because there are 2 values for the 01/01//018 in the yellow range, a SUB and a RED. (226,158.88 x 2). I do not know why my formula does not work.
– Bálint Ákos
Nov 14 '18 at 14:21
Also, I need to do it with INDEX MATCH because the underlying data is in another excel sheet and I cannot have it open all the time. with INDEX MATCH, you can have the underlying excel closed.
– Bálint Ákos
Nov 14 '18 at 14:28
not sure why you are checking on type, as your explanation of the expected output is just summing it by date (in disregards of type).
– Chrisvdberge
Nov 14 '18 at 14:48
btw, did you do Ctrl+Shft+Enter to make it an array formula?
– Chrisvdberge
Nov 14 '18 at 14:53
please clarify what the desired output is and how/why your current formula is not delivering the output you need Include a sample dataset and the formula(s) in your question
– Chrisvdberge
Nov 14 '18 at 12:57
please clarify what the desired output is and how/why your current formula is not delivering the output you need Include a sample dataset and the formula(s) in your question
– Chrisvdberge
Nov 14 '18 at 12:57
I need to sum up values in the blue range by looking up data in the yellow range by criteria (date & type). In J12 I am expecting 452,317.76, because there are 2 values for the 01/01//018 in the yellow range, a SUB and a RED. (226,158.88 x 2). I do not know why my formula does not work.
– Bálint Ákos
Nov 14 '18 at 14:21
I need to sum up values in the blue range by looking up data in the yellow range by criteria (date & type). In J12 I am expecting 452,317.76, because there are 2 values for the 01/01//018 in the yellow range, a SUB and a RED. (226,158.88 x 2). I do not know why my formula does not work.
– Bálint Ákos
Nov 14 '18 at 14:21
Also, I need to do it with INDEX MATCH because the underlying data is in another excel sheet and I cannot have it open all the time. with INDEX MATCH, you can have the underlying excel closed.
– Bálint Ákos
Nov 14 '18 at 14:28
Also, I need to do it with INDEX MATCH because the underlying data is in another excel sheet and I cannot have it open all the time. with INDEX MATCH, you can have the underlying excel closed.
– Bálint Ákos
Nov 14 '18 at 14:28
not sure why you are checking on type, as your explanation of the expected output is just summing it by date (in disregards of type).
– Chrisvdberge
Nov 14 '18 at 14:48
not sure why you are checking on type, as your explanation of the expected output is just summing it by date (in disregards of type).
– Chrisvdberge
Nov 14 '18 at 14:48
btw, did you do Ctrl+Shft+Enter to make it an array formula?
– Chrisvdberge
Nov 14 '18 at 14:53
btw, did you do Ctrl+Shft+Enter to make it an array formula?
– Chrisvdberge
Nov 14 '18 at 14:53
|
show 1 more comment
1 Answer
1
active
oldest
votes
1
=SUMPRODUCT((Table1[Date]=[@LookupDate])*(Table1[Type]="RED","SUB")*Table1[Value])
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
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
);
);
draft saved
draft discarded
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%2f53300100%2fsumif-index-match%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
1
=SUMPRODUCT((Table1[Date]=[@LookupDate])*(Table1[Type]="RED","SUB")*Table1[Value])
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
add a comment |
1
=SUMPRODUCT((Table1[Date]=[@LookupDate])*(Table1[Type]="RED","SUB")*Table1[Value])
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
add a comment |
1
1
1
=SUMPRODUCT((Table1[Date]=[@LookupDate])*(Table1[Type]="RED","SUB")*Table1[Value])
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
=SUMPRODUCT((Table1[Date]=[@LookupDate])*(Table1[Type]="RED","SUB")*Table1[Value])
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
answered Nov 14 '18 at 22:09
Michal RosaMichal Rosa
1,3221814
1,3221814
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53300100%2fsumif-index-match%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
please clarify what the desired output is and how/why your current formula is not delivering the output you need Include a sample dataset and the formula(s) in your question
– Chrisvdberge
Nov 14 '18 at 12:57
I need to sum up values in the blue range by looking up data in the yellow range by criteria (date & type). In J12 I am expecting 452,317.76, because there are 2 values for the 01/01//018 in the yellow range, a SUB and a RED. (226,158.88 x 2). I do not know why my formula does not work.
– Bálint Ákos
Nov 14 '18 at 14:21
Also, I need to do it with INDEX MATCH because the underlying data is in another excel sheet and I cannot have it open all the time. with INDEX MATCH, you can have the underlying excel closed.
– Bálint Ákos
Nov 14 '18 at 14:28
not sure why you are checking on type, as your explanation of the expected output is just summing it by date (in disregards of type).
– Chrisvdberge
Nov 14 '18 at 14:48
btw, did you do Ctrl+Shft+Enter to make it an array formula?
– Chrisvdberge
Nov 14 '18 at 14:53