Count Dates in a Table using Today's Date and only visible rows

[Jborgen]

Hello All,
I have a table I am using to create a dashboard from. The table holds multiple columns and one of them being a date column. What I need to do is be able to filter the table by person and then count the dates that are within the current week we are in (Sunday through Saturday). I have tried using the SumProduct but I am struggling to get it work properly.

 

[myall_blues]

It's not 100% clear what you're after, but hopefully I've interpreted correctly.
In the example below, the formula in J2 will return all the project IDs in the current week (Sunday to Saturday) assigned to John.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Project ID		PM					Due date
2	5900		John					28/08/2024		5900
3	5922		Paul					26/08/2024		6024
4	5657		George					20/08/2024		5948
5	5914		Ringo					29/08/2024		5957
6	6024		John					28/08/2024
7	6046		Paul					26/08/2024
8	5323		George					28/08/2024
9	6025		Ringo					2/09/2024
10	5948		John					30/08/2024
11	5322		Paul					3/09/2024
12	5537		George					3/09/2024
13	5771		Ringo					28/08/2024
14	5957		John					26/08/2024
15	5936		Paul					20/08/2024
16	5524		George					22/08/2024
Sheet2

Cell Formulas
Range	Formula
J2:J5	J2	=FILTER(A2:A16,(H2:H16>=1-WEEKDAY(TODAY())+TODAY())*(H2:H16<=7-WEEKDAY(TODAY())+TODAY())*(C2:C16="John"))
Dynamic array formulas.

 

[kvsrinivasamurthy]

Try. In K2.

=LET(a,$C$2:$C$16,dt,$H$2:$H$16,BYROW(a,LAMBDA(ro,IF(COUNTIF($C$1:ro,ro)=1,SUMPRODUCT((a=ro)*(dt>=(TODAY()-WEEKDAY(TODAY(),1)+1))*(dt<=(TODAY()+7-WEEKDAY(TODAY(),1)))),""))))

 

[Fluff]

Hi & welcome to MrExcel.
Another option that will not count hidden rows

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Project ID		PM					Due date		John
2	5900		John					25/08/2024		5
3	5922		Paul					26/08/2024
4	5657		George					20/08/2024
5	5914		Ringo					29/08/2024
6	6024		John					28/08/2024
7	6046		Paul					26/08/2024
8	5323		George					28/08/2024
9	6025		john					31/08/2024
10	5948		John					30/08/2024
11	5322		Paul					03/09/2024
12	5537		George					03/09/2024
13	5771		Ringo					28/08/2024
14	5957		John					26/08/2024
15	5936		Paul					20/08/2024
16	5524		George					22/08/2024
Data

Cell Formulas
Range	Formula
J2	J2	=LET(d,H2:H100,n,C2:C100,ROWS(FILTER(d,(WEEKNUM(+d,1)=WEEKNUM(TODAY(),1))*(n=J1)*(MAP(d,LAMBDA(m,SUBTOTAL(103,m)))))))

 

[Jborgen]

Try. In K2.

=LET(a,$C$2:$C$16,dt,$H$2:$H$16,BYROW(a,LAMBDA(ro,IF(COUNTIF($C$1:ro,ro)=1,SUMPRODUCT((a=ro)*(dt>=(TODAY()-WEEKDAY(TODAY(),1)+1))*(dt<=(TODAY()+7-WEEKDAY(TODAY(),1)))),""))))

Thank you. This is close. I feel like I need to explain a little more. Sorry.

So I have the Dashboard on one sheet where I want cell D21 to return a total count of dates that are in the current week based on today's date in a table on the PO Report sheet. On the Dashboard sheet I also have a filter that will be applied to the table for each person. For Example, Cfrank will be able to filter to their projects only and then I want the number on the Dashboard sheet to reflect the total number of dates within the current week based on today's date.

So the name is not really important in the formula. It's more about counting the dates in the table for the current week based on today's date when the table is filtered by any name. Additional screenshots to hopefully help.

Thanks!

 

[Fluff]

If your not interested in the name, try

=LET(d,H2:H100,n,C2:C100,ROWS(FILTER(d,(WEEKNUM(+d,1)=WEEKNUM(TODAY(),1))*(MAP(d,LAMBDA(m,SUBTOTAL(103,m)))))))

 

[Jborgen]

If your not interested in the name, try

=LET(d,H2:H100,n,C2:C100,ROWS(FILTER(d,(WEEKNUM(+d,1)=WEEKNUM(TODAY(),1))*(MAP(d,LAMBDA(m,SUBTOTAL(103,m)))))))

This worked Perfectly!!!!

Thank you!!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Jborgen]

Glad we could help & thanks for the feedback.

Sorry to bother you again.
What if I wanted the dates in the Next Week. Not a rolling 7 days from TODAY but just dates that occur in the next week Sunday to Saturday?
Thanks!

 

[Fluff]

Just add 7 to the TODAY function.