As the ranges are on different sheets, when you add or delete a row on one sheet, the number of cells referred to in each part of the formula is no longer equal.
In SUMPRODUCT() each part must reference the same number of cells.
When I tried this, I started with ranges of C2 to C50 and F2 to F50 on both worksheets, but deleting a row on the Closed Issues worksheet changed the reference to C2 to C49 and F2 to F49, but the ranges on the Open Issues worksheet remained 2 to 50.
Hence the NA error.
I was able to handle this by using change events on the two worksheets Open / Closed Issues and using named ranges.
First I defined four named ranges, rng1, rng2, rng3 and rng4
These were rng1 and rng2 for Open Issues C2 to C50 and F2 to F50
and rng3 and rng4 for Closed Issues C2 to C50 and F2 to F50
The formula is now rewritten as:
Goto the Closed Issues worksheet and right-click on the Tab name and select view code.
Add this code to the VB window that opens:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Names("rng3").RefersTo = ActiveSheet.Range("C2:C50")
ActiveWorkbook.Names("rng4").RefersTo = ActiveSheet.Range("F2:F50")
Do the same on the Open Issues worksheet using rng1 and rng2 in place of rng3 and rng4
Now, whenever a row is added or deleted on either sheet, the ranges remain set to rows 2 to 50.
You will have to determine if this still returns the required results from your formula, but you don't get NA errors.
I didn't do any more than basic code here - it would probably be appropriate to ensure that this code only runs when rows are added or deleted and probably you should disable change events while the code is running.
If this works for you, these extras could be added to make the code more robust.