Count (countifs) norm exceedances with variable dates and variable norms formula or VBA

Question

I have substance measurement data from water quality checks.

The data is listed in a table starting with the date (dd/mm/yyyy). Above the table are the norms. The variable norms are in green. Those norms are generated on a second tab, and change (sort of) weekly. I need to count the norm exceedances by year, but those depend on the dates and the corresponding norms which are added on in the second tab.

A norm exceedance is defined in two ways:

• everything above the norm if 'schepstaal'
• everything above the norm+30% if 'D'

I have a countifs formula for the fixed norms, but not for the variable ones. (I normally hide the countif rows.)

e.g.:

=COUNTIFS(Table128383941[[Datum]:[Datum]];">=01/01/2022";Table128383941[[Datum]:[Datum]];"<=31/12/2022";Table128383941[BZV (mg/l)];">"&F11)

e.g.:

=COUNTIFS(Table128383941[[Datum]:[Datum]];">=01/01/2022";Table128383941[[Datum]:[Datum]];"<=31/12/2022";Table128383941[[Soort staalname]:[Soort staalname]];"=schepstaal";Table128383941[BZV (mg/l)];">="&2F11)+COUNTIFS(Table128383941[[Datum]:[Datum]];">=01/01/2022";Table128383941[[Datum]:[Datum]];"<=31/12/2022";Table128383941[[Soort staalname]:[Soort staalname]];"=D";Table128383941[BZV (mg/l)];">="&F11+(F11/10030))

I need to find a way where if a measurement falls between dates it only uses those norms from the list between the corresponding dates, and have the year total. And I cannot ask the client to add a new countif line each week.

The solutions may preferably be a formula or VBA if not possible.

tabs 1 and 2: