Filter Count & Sum Based on Given Criterias

I have an excel workbook with a Database worksheet, this worksheet has a table RecordsTable. The important columns to note are: REG.DATE and CARD

The RecordsTable[REG.DATE] are stored as Date month/day/year asin 8/7/2023 and 8/28/2023 and 12/28/2023 The RecordsTable[CARD] has values as in prices stored with Numbers eg: 500.00, 2900.23 etc.

In a different Worksheet CardRevenue, I am trying to use these filter conditions:

1. DAY, WEEK, MONTH, YEAR (Datavalidation List)
2. 2023, 2022, 2021...etc (Datavalidation List)

DAY - Match any date that matches Today's DAY 
WEEK - Match any date that matches today's WEEK.
MONTH - Match any date that matches today's MONTH
YEAR - Mathc any data that matches today's YEAR.

and the second filter is used to check the ACTUAL YEAR.

For example, If I have;

REG.DATE            CARD

8/7/2023            200.00

8/28/2023           300.00

12/28/2023          500.00

And I select filter:

DAY and 2022 -> Returns count = 0, sum =0 in it's respective field
DAY and 2023 -> Returns count = 1, sum 300.00

WEEK and 2022 -> Returns count = 0, sum = 0.
WEEK and 2023 -> Return count = 1, sum = 300.00 (today is the begining of the week and it is 28th)


MONTH and 2022 -> Returns count = 0, sum = 0.
MONTH and 2023 -> Return count = 2, sum = 500.00

YEAR  and 2022 -> 0
Year  and 2023 -> count = 3, sum = 1000.00

My current code seem to work with DAY, MONTH AND YEAR except WEEK.

My Implementation:

A7:

=IF(OR(H5="Day", H5="Week", H5="Month", H5="Year"),
   IF(H5="Day",
      SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(DAY(RecordsTable[REG.DATE])=DAY(TODAY()))*(RecordsTable[CARD]>0)),
      IF(H5="Week",
         SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(WEEKNUM(RecordsTable[REG.DATE])=WEEKNUM(TODAY()))*(RecordsTable[CARD]>0)),
         IF(H5="Month",
            SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(RecordsTable[CARD]>0)),
            IF(H5="Year",
               SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(RecordsTable[CARD]>0)),
               0
            )
         )
      )
   ),
   SUMPRODUCT((YEAR(RecordsTable[REG.YEAR])=L5)*(RecordsTable[CARD]>0))
)

G7:

=IF(OR(H5="Day", H5="Week", H5="Month", H5="Year"),
   IF(H5="Day",
      SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(DAY(RecordsTable[REG.DATE])=DAY(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
      IF(H5="Week",
         SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(WEEKNUM(RecordsTable[REG.DATE])=WEEKNUM(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
         IF(H5="Month",
            SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
            IF(H5="Year",
               SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(RecordsTable[CARD]>0), RecordsTable[CARD]),
               0
            )
         )
      )
   ),
   SUMPRODUCT((YEAR(RecordsTable[REG.YEAR])=L5)*(RecordsTable[CARD]>0), RecordsTable[CARD])
)

When I select WEEK and any YEAR, I GET #VALUE! in both fileds with error: A value used in this formula is of wrong datatype.

NOTE: If there are more clarifications neede, I will be glad to provide. Any help at this point will be appreciated I have been on this for 3 days.

Sorry this has made it day 4 for you but this following one should work:

=IF(OR(H5="Day"; H5="Week"; H5="Month"; H5="Year");
   IF(H5="Day";
      SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(DAY(RecordsTable[REG.DATE])=DAY(TODAY()))*(RecordsTable[CARD]>0); RecordsTable[CARD]);
      IF(H5="Week";
         SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(BYROW(RecordsTable[REG.DATE];LAMBDA(array; WEEKNUM(array) = WEEKNUM(TODAY()))))*(RecordsTable[CARD]>0); RecordsTable[CARD]);
         IF(H5="Month";
            SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(RecordsTable[CARD]>0); RecordsTable[CARD]);
            IF(H5="Year";
               SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(RecordsTable[CARD]>0); RecordsTable[CARD]);
               0
            )
         )
      )
   );
   SUMPRODUCT((YEAR(RecordsTable[REG.YEAR])=L5)*(RecordsTable[CARD]>0); RecordsTable[CARD])
)

I used BYROW(RecordsTable[REG.DATE];LAMBDA(array; WEEKNUM(array) = WEEKNUM(TODAY()))) instead of WEEKNUM(RecordsTable[REG.DATE])=WEEKNUM(TODAY())
because the WEEKNUM() function doesn't accept a range.

Hope this helps :)