Forum Discussion
Count a value in a range which depends on start and end date
Hi Everyone, I need to count how many absent days for each class in a period can be determinded by user. User can choose start date in Cell (AM1) and end date in Cell (AO1). Then in range AO7:AO11...
=SUMPRODUCT(($E$1:$AI$1>=$AM$1)*($E$1:$AI$1<=$AO$1)*($E$2:$AI$7="A")*($D$2:$D$7=AN7))
SUMPRODUCT returns the expected result in my sheet.
An insiders beta solution
= GROUPBY(
class,
BYROW(
attendance,
LAMBDA(a,
COUNTIFS(
a, "A",
timeline, ">=" & periodStart,
timeline, "<=" & periodEnd
)
)
),
SUM, , 0
)The approach is to count the absences within the period for each row using BYROW/COUNTIFS and then group the result by class using GROUP/SUM.
Thanks a lot peter
