Array formula with if conditions and substraction-CodePudding

I am searching for a formula solution that would summarize hours based on two conditions (date - column a, activity - column b). More precise, I want to summarize hours of sleep each day with array formula that would include whole column range.

The data looks like this:

When I define exact range the formula works.

{=IF(A2:A10=I$6;IF(B2:B10="Sleep";(D2:D10)-(C2:C10);0);0)}

But when I try to include whole column it returns 0.

{=IF(A:A=I$6;IF(B:B="Sleep";(D:D)-(C:C);0);0)}

Thank you!

CodePudding user response：

Take a look at this link for some little-known insight from Microsoft. Microsoft deliberately prevents the use of entire columns in some formulas that internally use arrays:

=SUMPRODUCT(((Schedule[End] (Schedule[Start]>Schedule[End]))-Schedule[Start])*(Schedule[Activity]="Sleep")*(Schedule[Date]=I6))

but you should recognise that the organisation of your data does not permit the (by-date) analysis you require, e.g. most of the sleep attributable to Jan 22nd is properly attributable to Jan 23rd but that can't be reflected in the summary, as you have treated the end time on Jan 22nd as being 16 hours before the start time...

(cells I7:K7 have the custom number format [hh]:mm)