Thanks for your answers. In the end I decided that writing my own
replacement VBA function for SUMIF would be the simplest way (I have
some hefty formulas to put in the middle of this sumif function
anyway). For completeness I've posted the code below.

Public Function SumIfNB(rngSource As Range, vMatchVal As Variant,
rngSum As Range) As Variant
'This functions works like sumif but with one exception.
'If all matching values in the row to sum are blank it returns blank
On Error GoTo ErrHe
Dim i As Long
Dim vSum As Variant
Dim bAllBlank As Boolean
bAllBlank = True

For i = 1 To rngSource.Cells.Rows.Count - 1
If rngSource.Cells(i, 1).Value < "" Then
If rngSource.Cells(i, 1).Value = vMatchVal Then
If rngSum.Cells(i, 1).Value < "" Then
bAllBlank = False
vSum = vSum + rngSum.Cells(i, 1).Value
End If
End If
End If
Next i

If bAllBlank = True Then
SumIfNB = ""
Else
SumIfNB = vSum
End If
ExitHe
Exit Function

ErrHe
MsgBox Err.Description
Resume ExitHere

End Function