Forum Discussion
Count a value in a range which depends on start and end date
=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.
Hello Eyad_Kurdi,
you are welcome. I hope you are doing very well too.
=LET(z,DROP(REDUCE("",C3:C8,LAMBDA(u,v,
VSTACK(u,
LET(student_range,FILTER(E3:AI8,C3:C8=v),
absent_days_range,FILTER(E1:AI1,student_range="A"),
absent_days_range_filtered,FILTER(absent_days_range,(absent_days_range>=AM1)*(absent_days_range<=AO1)),
count_absent_days,COUNT(absent_days_range_filtered),
IF(count_absent_days=0,{"".""},
HSTACK(
IFNA(EXPAND(v,count_absent_days),v),
TRANSPOSE(absent_days_range_filtered))))))),1),
LET(count_all,COUNT(TAKE(z,,-1)),
VSTACK(
{"ID"."Name"."Days"},
HSTACK(SEQUENCE(count_all),FILTER(z,LEN(TAKE(z,,1))>0)))))
This formula works in my sample sheet. However it only works with Office 365 and Excel for the web. Functions such as LAMBDA and REDUCE aren't available in older versions of Excel.
Dear OliverScheurich
I have a new approach that there are two types of attendance A and P
If I wanna list all days for both types and add a new column refer to the absent type like in the attached file AN21:AN28
and modify the old formula in cell AK21 to accept both A AND P
I wonder if you will help me this time ,,,, so embarrassed of your kindness 🙂
Hello, attached is my suggestion for this scenario. Kind regards.
