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

But SUMPRODUCT already has this feature.

Example

=SUMPRODUCT(--(A1:A10="apple"),--(B1:B10>3),C1:C10)

best wishes

Answer 2

Hi,

This array formula (Ctrl+Shift+Enter) works

=SUM(IF($A$2:$A$17="B",IF(B2:B17>2,B2:B17*C2:C17*D2:D17,0)))

Hope this helps.

Answer 3

DataType	DataX	DataY	DataZ
A	1	23	#N/A
A	2	70	71
A	#N/A	35	#N/A
A	4	106	109
A	5	53	#N/A
B	2	160	161
B	3	80	82
B	5	40	44
B	7	20	26
B	11	10	20
C	5	5	#N/A
C	11	16	26
C	15	8	22
C	#N/A	4	#N/A
C	#N/A	2	1
C	#N/A	1	0

= SumProductIfs(DataX,DataY,DataZ,,, DataType,"B", DataX,">2") should be 3*80*82 + 5*40*44 + 7*20*26 + 11*10*20.

The SumProduct(--(test)…) technique, though clever, is confounded by the #N/As.

Answer 4

SUMPRODUCT function already supports this requirement.

The syntax is not the same but functionality is same.

eg.

=SUMPRODUCT(--(A2:A23="Mon"),--(B2:B23="Jan"),C2:C23)

will Sum C2:C23 for corresponding rows where Col A has Mon and Col B has Jan.

Answer 5

I didn’t know that could be done as a non-array formula. Thank you. 

Request withdrawn.