COUNTIF type Formula Array

Topic

New Reply

I have a problem with the formula arrays in the attached workbook L2:L17. The current formula array is =COUNTIF($E2:$E8,”0″)*$K$1. However, I need L2:L17 to calculate work week hours that will exclude weekends and designated holidays from a LOOKUP table. I understand the limitations of the COUNTIF worksheet function, but there must be another way that doesn’t involve converting this to a pivot table. Perhaps changing up the company holiday lookup table? I don’t know. Any help would be much appreciated.

Amy

Reply | Quote

Replies

Try this in L3:

=SUMPRODUCT(($C$2:$C$106=I3)*($F$2:$F$106>1)*($F$2:$F$106<7)*(ISERROR($G$2:$G$106)))*$K$1

This formula can be filled down.

Reply | Quote

That worked perfectly. Thank you.

Amy

Reply | Quote