Uses the C API to retrieve the used range for a sheet, and intersect with a passed-in ExcelReference

Excel-DNA Used Range.cs

using ExcelDna.Integration;
using static System.Math;
using static ExcelDna.Integration.XlCall;

public static class TestFunctions
{
    [ExcelFunction(Description = "Returns the sum of cells in the target (using the used range for performance)", IsMacroType = true)]
    public static object GetUsedSum([ExcelArgument(AllowReference = true)] object target)
    {
        // NOTE: This function will be volatile by default,
        // since it is both IsMacroType=true and has an AllowReference=true.
        // So we might want to reset the volatile flag for every call.
        // WARNING: However, clearing the volatile flag also means that the function will not recalculate when values in the 
        //          target range change due to 'volatile' recalculations, e.g. if the target range has an =RAND() cell,
        //          then pressing F9 will change that cell, but this function would not be called. 
        // Excel(xlfVolatile, false);

        if (!(target is ExcelReference))
            return ExcelError.ExcelErrorValue;

        object targetSheetName = Excel(xlSheetNm, target);

        // The used range rows and columns here are 1-based (returning 0 for an empty sheet)
        // We adjust to be 0-based, to match the ExcelReference convention
        // For an empty sheet (where we now get RowLast = ColumnLast = -1) the ExcelReference will convert the -1 values back to 0, 
        // so will get an ExcelEmpty from GetValue() and eventually (correctly) return 0 from the function.
        int firstRow = (int)(double)Excel(xlfGetDocument, 9, targetSheetName) - 1;
        int lastRow  = (int)(double)Excel(xlfGetDocument, 10, targetSheetName) - 1;
        int firstCol = (int)(double)Excel(xlfGetDocument, 11, targetSheetName) - 1;
        int lastCol  = (int)(double)Excel(xlfGetDocument, 12, targetSheetName) - 1;
        
        ExcelReference targetRef = (ExcelReference)target;

        ExcelReference usedTarget = new ExcelReference(
                rowFirst: Max(firstRow, targetRef.RowFirst),
                rowLast: Min(lastRow, targetRef.RowLast),
                columnFirst: Max(firstCol, targetRef.ColumnFirst),
                columnLast: Min(lastCol, targetRef.ColumnLast),
                sheetId: targetRef.SheetId);
        
        object values = usedTarget.GetValue();

        double sum = 0;
        if (values is object[,])
        {
            object[,] valuesArr = (object[,])values;
            for (int i = 0; i < valuesArr.GetLength(0); i++)
            {
                for (int j = 0; j < valuesArr.GetLength(1); j++)
                {
                    var value = valuesArr[i,j];
                    if (value is double)
                    {
                        sum += (double)value;
                    }
                }
            }
        }
        else if (values is double)
        {
            // Single cell
            sum = (double)values;
        }
        return sum;
    }
}