Sure, xor and thanks for the reply. File attached. I was able to use sumproduct in columns AT and AU.

Attached Files

Forum_Test.xlsx (443.5 KB, 11 views)

Does the formula in sheet Activity, column E return what you expect?

Could you please try to fill in manually some expected values in column F and G.

Can it happen that more holidays appear in a single row on sheet Activity (column a:d)
If YES, what should be returned?

Attached Files

ExcelForum_DateRangeArrays_2.xlsx (110.1 KB, 13 views)

Hi xor,

Thanks for the reply. Per your request, I've entered some manual values into columns F and G. There shouldn't be any instances in which a date range in a row between columns A should contain more than one holiday as separately, I'll be building in an exception column that will flag any record where worked-arrival is greater than 14. (don't believe there are any US holidays that occur within 2 weeks of each other).

As for the formula in column E, this is great however, it does return incorrect values when column D is blank.

Thanks again for your help. I was stuck thinking that I had to use INDEX MATCH SUMPRODUCT and not just INDEX SUMPRODUCT. That alone was a great learning experience.

Attached Files

ExcelForum_DateRangeArrays_3.xlsx (110.5 KB, 10 views)