Excel: Count cells in matrix with multiple conditions
up vote
1
down vote
favorite
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 at 11:49
ColdBrew
82
New contributor
ColdBrew 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 |
up vote
1
down vote
favorite
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 at 11:49
ColdBrew
82
New contributor
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 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 at 9:47
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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 at 11:49
ColdBrew
82
New contributor
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 at 11:49
ColdBrew
82
New contributor
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 23 at 11:49
ColdBrew
82
New contributor
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 23 at 11:49
ColdBrew
82
New contributor
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 23 at 11:49
ColdBrew
82
asked Nov 23 at 11:49
ColdBrew
82
82
New contributor
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
ColdBrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 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 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 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 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 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 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 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 at 9:47
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 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
yesterday
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
yesterday
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 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
yesterday
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
yesterday
add a comment |
up vote
0
down vote
accepted
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 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
yesterday
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
yesterday
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 at 16:10
XOR LX
1,05757
Switch to SUMPRODUCT:
=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))
Regards
answered Nov 24 at 16:10
XOR LX
1,05757
answered Nov 24 at 16:10
XOR LX
1,05757
answered Nov 24 at 16:10
XOR LX
1,05757
answered Nov 24 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
yesterday
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
yesterday
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
yesterday
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
yesterday
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
yesterday
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
yesterday
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
yesterday
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
yesterday
add a comment |
ColdBrew is a new contributor. Be nice, and check out our Code of Conduct.
ColdBrew is a new contributor. Be nice, and check out our Code of Conduct.
ColdBrew is a new contributor. Be nice, and check out our Code of Conduct.
ColdBrew 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%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 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 at 9:47