The following formula works flawlessly:
=SUMPRODUCT((ShiftSelectValues=Template!B7:O7)*ShiftHours)
Where shiftselectedvalues and shifthours are A2:A52 and C2:C52 respectively.
However if I change the matching criteria to use a column range instead of a row range, for example B2:B30, the calculation fails with #NA. With the evaluate formula tool it looks like matching is causes #NA rather then producing a true or false.
Anyone know why this is happening and how to fix it. Let me know if you need the spreadsheet. Thanks
Bookmarks