SOLVED

# Count a value in a range which depends on start and end date

Copper Contributor

# 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 count how many A for each class.

I am really appreciate your help

12 Replies

# Re: Count a value in a range which depends on start and end date

I could count for one day and 1 row (E3:AI3) only but not for a period... =COUNTIFS(INDEX(\$E\$3:\$AI\$18;;XMATCH(\$AM\$1;\$E\$1:\$AI\$1;0);1);"A";\$D\$3:\$D\$18;\$AN7)
best response confirmed by Eyad_Kurdi (Copper Contributor)
Solution

# Re: 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.

# Re: Count a value in a range which depends on start and end date

Dear @OliverScheurich,

It works!  how amazing, simple, clean, clear it is ...

More than appreciated...

Thanks a lot for your very very fast help

# Re: Count a value in a range which depends on start and end date

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

# Re: Count a value in a range which depends on start and end date

=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.

# Re: Count a value in a range which depends on start and end date

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.

# Re: Count a value in a range which depends on start and end date

Dear @OliverScheurich

Hi again, I hope you are doing very well...

U helped me a lot on the same issue, but I have one requirement in the same subject

If I need to list all the absent days for all students like in table AK21:AM33 between 2 periods same as before,

how can I do it for each student who has absent days...

More than appreciated ...

# Re: Count a value in a range which depends on start and end date

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.

# Re: Count a value in a range which depends on start and end date

Dear OliverScheurich,
Finding the right words to convey my deep appreciation for everything you do is quite challenging.

# Re: Count a value in a range which depends on start and end date

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 :)

# Re: Count a value in a range which depends on start and end date

Hello, attached is my suggestion for this scenario. Kind regards.

# Re: Count a value in a range which depends on start and end date

Thanks a lot @peter
1 best response

Accepted Solutions
best response confirmed by Eyad_Kurdi (Copper Contributor)
Solution

# Re: 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.