1

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!

  • 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
0

Switch to SUMPRODUCT:

=SUMPRODUCT((Table1[[column_1]:[column_n]]<TODAY())*(Table1[number]=0))

Regards

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.