Function request: SumProductIfs

Question

There is a function

    SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Please could there be a function SumProductIfs, with exactly five columns that can be producted. (I would use five, but would never use more.)

    SumProductIfs(

        sumproduct_range1, sumproduct_range2, sumproduct_range3, sumproduct_range4, sumproduct_range5,

        criteria_range1, criteria1, [criteria_range2, criteria2], ...

    )

though presumably named in all capitals.

SumProductIfs would work in the entirely obvious way, with missing sum_productrangen’s being treated as a constant 1. 

And please include in both PC and Mac versions, so that those writing cross-compatible spreadsheets may use it. 

(Non-MS readers: please encourage MS to do this by posting support, and if you have, improvements.)

Answer 1

Hi,

Share some data, explain the problem and show the expected result.

Answer 2

Apologies, I have recalled the problem. Yes, the conditions in SumProduct() work iff there are no errors. But SumIfs() works correctly even if some of the data is NA() — that deemed being deemed to fail to the test — and it would be very helpful if there was similar functionality for non-array-formulae SumProduct(). 

Request re-instated.