Countifs / hlookup based on conditions

[jamesy_123]

Hi,

I need some assistance with formulas to determine the Week Ending & Following Week Statistics. I need each of the column D personnel to count the cells if the cell contains either 12, FIA or FOP, for the date range provided in Cell D3.
I have manually entered some of the numbers which should appear in Columns D & E.

Your assistance would be greatly appreciated

[AliGW]

Welcome to the forum.

Cell D3 does not contain a date RANGE, rather a single date, so how do you want it interpreting? Is it the week start date?

[AliGW]

OK. Try:

=COUNTA(FILTER(O5:AB5,($O$4:$AB$4>D$3-7)*($O$4:$AB$4<=D$3)*((O5:AB5=12)+(O5:AB5="FIA")+(O5:AB5="FOP"))))

=COUNTA(FILTER(O5:AB5,($O$4:$AB$4>=D$3)*($O$4:$AB$4<D$3+7)*((O5:AB5=12)+(O5:AB5="FIA")+(O5:AB5="FOP"))))

[jamesy_123]

Thank you for your reply.
This works well, except for in any rows where there is no information in column O:AB this pastes 1, instead of 0 (see the yellow highlighted cells). Any idea on how to fix?

Attachment 842917

[HansDouwe]

Please replace COUNTA with COUNT (COUNTA counts also #VALUE if the FILTER function returns #VALUE)

or

replace COUNTA(FILTER(O5:AB5, with SUM( and remove 1 bracket at the end of the formula.