Excel: Count cells in matrix with multiple conditions
I have a table table1 with a matrix of n columns [[column_1]:[column_n]] that contain dates or empty fields.
Also there's a column [number] with integers.
I want to count all cells that contain a date in the past AND are in a row where the corresponding number is 0. How do I do that?
Only the first condition is easy: =COUNTIF(Table1[[column_1]:[column_n]];"<"&TODAY())
But I don't get it to only consider rows in which the integer is 0.
I know there's probably an approach using array functions, but I can't figure it out. Searching on google was to no avail.
Thanks for your help!
microsoft-excel worksheet-function
asked Nov 23 '18 at 11:49
ColdBrew
82
add a comment |
I have a table table1 with a matrix of n columns [[column_1]:[column_n]] that contain dates or empty fields.
Also there's a column [number] with integers.
I want to count all cells that contain a date in the past AND are in a row where the corresponding number is 0. How do I do that?
Only the first condition is easy: =COUNTIF(Table1[[column_1]:[column_n]];"<"&TODAY())
But I don't get it to only consider rows in which the integer is 0.
I know there's probably an approach using array functions, but I can't figure it out. Searching on google was to no avail.
Thanks for your help!
microsoft-excel worksheet-function
asked Nov 23 '18 at 11:49
ColdBrew
82
1
Unfortunately Excel is not good at working with 2D ranges and lists at the same time. You need to count the values by row (=COUNTIF(Table1[@[column_1]:[column_n]];"<"&TODAY())*(table1[@[number]]=0)) and summarize them in a second step. (Don't forget the@in the formula)
– Máté Juhász
Nov 23 '18 at 12:24
Well, thanks! I guess there is a limit of what you can do without VBA, but good to know.
– ColdBrew
Nov 24 '18 at 9:47
add a comment |
I have a table table1 with a matrix of n columns [[column_1]:[column_n]] that contain dates or empty fields.
Also there's a column [number] with integers.
I want to count all cells that contain a date in the past AND are in a row where the corresponding number is 0. How do I do that?
Only the first condition is easy: =COUNTIF(Table1[[column_1]:[column_n]];"<"&TODAY())
But I don't get it to only consider rows in which the integer is 0.
I know there's probably an approach using array functions, but I can't figure it out. Searching on google was to no avail.
Thanks for your help!
microsoft-excel worksheet-function
asked Nov 23 '18 at 11:49
ColdBrew
82
I have a table table1 with a matrix of n columns [[column_1]:[column_n]] that contain dates or empty fields.
Also there's a column [number] with integers.
I want to count all cells that contain a date in the past AND are in a row where the corresponding number is 0. How do I do that?
Only the first condition is easy: =COUNTIF(Table1[[column_1]:[column_n]];"<"&TODAY())
But I don't get it to only consider rows in which the integer is 0.
I know there's probably an approach using array functions, but I can't figure it out. Searching on google was to no avail.
Thanks for your help!
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Nov 23 '18 at 11:49
ColdBrew
82
asked Nov 23 '18 at 11:49
ColdBrew
82
asked Nov 23 '18 at 11:49
ColdBrew
82
asked Nov 23 '18 at 11:49
ColdBrew
82
asked Nov 23 '18 at 11:49
ColdBrew
82
82
1
Unfortunately Excel is not good at working with 2D ranges and lists at the same time. You need to count the values by row (=COUNTIF(Table1[@[column_1]:[column_n]];"<"&TODAY())*(table1[@[number]]=0)) and summarize them in a second step. (Don't forget the@in the formula)
– Máté Juhász
Nov 23 '18 at 12:24
Well, thanks! I guess there is a limit of what you can do without VBA, but good to know.
– ColdBrew
Nov 24 '18 at 9:47
add a comment |
1
Unfortunately Excel is not good at working with 2D ranges and lists at the same time. You need to count the values by row (=COUNTIF(Table1[@[column_1]:[column_n]];"<"&TODAY())*(table1[@[number]]=0)) and summarize them in a second step. (Don't forget the@in the formula)
– Máté Juhász
Nov 23 '18 at 12:24
Well, thanks! I guess there is a limit of what you can do without VBA, but good to know.
– ColdBrew
Nov 24 '18 at 9:47
1
1
Unfortunately Excel is not good at working with 2D ranges and lists at the same time. You need to count the values by row (
=COUNTIF(Table1[@[column_1]:[column_n]];"<"&TODAY())*(table1[@[number]]=0)) and summarize them in a second step. (Don't forget the @ in the formula)– Máté Juhász
Nov 23 '18 at 12:24
Unfortunately Excel is not good at working with 2D ranges and lists at the same time. You need to count the values by row (
=COUNTIF(Table1[@[column_1]:[column_n]];"<"&TODAY())*(table1[@[number]]=0)) and summarize them in a second step. (Don't forget the @ in the formula)– Máté Juhász
Nov 23 '18 at 12:24
Well, thanks! I guess there is a limit of what you can do without VBA, but good to know.
– ColdBrew
Nov 24 '18 at 9:47
Well, thanks! I guess there is a limit of what you can do without VBA, but good to know.
– ColdBrew
Nov 24 '18 at 9:47
add a comment |
1 Answer
1
active
oldest
votes
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 '18 at 16:10
XOR LX
1,05757
That seems like the way to go! And it returns a reasonable value, except that it also counts empty cells as dates that lie in the future. Is there a way to omit those?
– ColdBrew
Nov 27 '18 at 22:01
Ok, I found a solution for this. (albeit not very elegant) =SUMPRODUCT((IF(ISEMPTY(Table1[[column_1]:[column_n]]);FALSE();Table1[[column_1]:[column_n]])<TODAY())*(Table1[number]=0)) I'll still count your solution as correct, as it answered my original question
– ColdBrew
Nov 27 '18 at 22:08
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
});
}
});
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%2f1377789%2fexcel-count-cells-in-matrix-with-multiple-conditions%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
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 '18 at 16:10
XOR LX
1,05757
That seems like the way to go! And it returns a reasonable value, except that it also counts empty cells as dates that lie in the future. Is there a way to omit those?
– ColdBrew
Nov 27 '18 at 22:01
Ok, I found a solution for this. (albeit not very elegant) =SUMPRODUCT((IF(ISEMPTY(Table1[[column_1]:[column_n]]);FALSE();Table1[[column_1]:[column_n]])<TODAY())*(Table1[number]=0)) I'll still count your solution as correct, as it answered my original question
– ColdBrew
Nov 27 '18 at 22:08
add a comment |
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 '18 at 16:10
XOR LX
1,05757
That seems like the way to go! And it returns a reasonable value, except that it also counts empty cells as dates that lie in the future. Is there a way to omit those?
– ColdBrew
Nov 27 '18 at 22:01
Ok, I found a solution for this. (albeit not very elegant) =SUMPRODUCT((IF(ISEMPTY(Table1[[column_1]:[column_n]]);FALSE();Table1[[column_1]:[column_n]])<TODAY())*(Table1[number]=0)) I'll still count your solution as correct, as it answered my original question
– ColdBrew
Nov 27 '18 at 22:08
add a comment |
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 '18 at 16:10
XOR LX
1,05757
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 '18 at 16:10
XOR LX
1,05757
answered Nov 24 '18 at 16:10
XOR LX
1,05757
answered Nov 24 '18 at 16:10
XOR LX
1,05757
answered Nov 24 '18 at 16:10
XOR LX
1,05757
1,05757
That seems like the way to go! And it returns a reasonable value, except that it also counts empty cells as dates that lie in the future. Is there a way to omit those?
– ColdBrew
Nov 27 '18 at 22:01
Ok, I found a solution for this. (albeit not very elegant) =SUMPRODUCT((IF(ISEMPTY(Table1[[column_1]:[column_n]]);FALSE();Table1[[column_1]:[column_n]])<TODAY())*(Table1[number]=0)) I'll still count your solution as correct, as it answered my original question
– ColdBrew
Nov 27 '18 at 22:08
add a comment |
That seems like the way to go! And it returns a reasonable value, except that it also counts empty cells as dates that lie in the future. Is there a way to omit those?
– ColdBrew
Nov 27 '18 at 22:01
Ok, I found a solution for this. (albeit not very elegant) =SUMPRODUCT((IF(ISEMPTY(Table1[[column_1]:[column_n]]);FALSE();Table1[[column_1]:[column_n]])<TODAY())*(Table1[number]=0)) I'll still count your solution as correct, as it answered my original question
– ColdBrew
Nov 27 '18 at 22:08
That seems like the way to go! And it returns a reasonable value, except that it also counts empty cells as dates that lie in the future. Is there a way to omit those?
– ColdBrew
Nov 27 '18 at 22:01
That seems like the way to go! And it returns a reasonable value, except that it also counts empty cells as dates that lie in the future. Is there a way to omit those?
– ColdBrew
Nov 27 '18 at 22:01
Ok, I found a solution for this. (albeit not very elegant) =SUMPRODUCT((IF(ISEMPTY(Table1[[column_1]:[column_n]]);FALSE();Table1[[column_1]:[column_n]])<TODAY())*(Table1[number]=0)) I'll still count your solution as correct, as it answered my original question
– ColdBrew
Nov 27 '18 at 22:08
Ok, I found a solution for this. (albeit not very elegant) =SUMPRODUCT((IF(ISEMPTY(Table1[[column_1]:[column_n]]);FALSE();Table1[[column_1]:[column_n]])<TODAY())*(Table1[number]=0)) I'll still count your solution as correct, as it answered my original question
– ColdBrew
Nov 27 '18 at 22:08
add a comment |
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%2f1377789%2fexcel-count-cells-in-matrix-with-multiple-conditions%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
1
Unfortunately Excel is not good at working with 2D ranges and lists at the same time. You need to count the values by row (
=COUNTIF(Table1[@[column_1]:[column_n]];"<"&TODAY())*(table1[@[number]]=0)) and summarize them in a second step. (Don't forget the@in the formula)– Máté Juhász
Nov 23 '18 at 12:24
Well, thanks! I guess there is a limit of what you can do without VBA, but good to know.
– ColdBrew
Nov 24 '18 at 9:47