Forum Discussion
Count a value in a range which depends on start and end date
- Apr 17, 2024
=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.
=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.
Dear OliverScheurich
Thanks again for your great help,
I wonder what is the best way to list all the absent days between 2 dates (Cells AM1 and AO1) for certain student (cell AL6) in a column AL10:AL19
Thanks in advanced
- OliverScheurichApr 18, 2024Gold Contributor
=TRANSPOSE(FILTER(FILTER(E1:AI1,FILTER(E3:AI8,C3:C8=AL6)="A"),(FILTER(E1:AI1,FILTER(E3:AI8,C3:C8=AL6)="A")>=AM1)*(FILTER(E1:AI1,FILTER(E3:AI8,C3:C8=AL6)="A")<=AO1),"no absent days"))
You are welcome. If you have access to the FILTER function you can use this formula which is in cell AL10 and spills the results.
=LET(student_range,FILTER(E3:AI8,C3:C8=AL6),absent_days_range,FILTER(E1:AI1,student_range="A"),TRANSPOSE(FILTER(absent_days_range,(absent_days_range>=AM1)*(absent_days_range<=AO1),"no absent days")))
If you have access to the FILTER and LET function you can use this formula which is in cell AM10 and spills the results.
Actually i don't like especially the first formula and i think that there should be a shorter and cleaner solution. However the formula returns the expected result in my sample sheet.