Value Error in Aggregate for Sum function

[hemantparmar]

Hi,

I am trying to use Aggregate function for calculating SUM using a criteria.

The product codes are in C2:C15
The qty sold are in D2:D15

I am using the below formula:
=AGGREGATE(9,6,D2:D15*(C2:C15=C17))

C17 = has the product code for which I want to find the sum of qty.

When I select the array argument of the Aggregate function in the above formula (i.e. D2:D15*(C2:C15=C17)) and press F9, i get : {0;0;92;0;106;0;0;0;0;0;0;108;0;0}

Why the Aggregate function is not returning the sum of the values in the above array?
I even tried using division instead of multiplication in the array. i.e. D2:D15/(C2:C15=C17)

Pls help.

rgds
hemant

[johnmpl]

Hi, hemantparmar!

the AGGREGATE function only works with arrays with operations between 14 and 19.

If you want to sum with condition, you can use SUMPRODUCT or SUMIF instead:

=SUMPRODUCT(D2:D15,--(C2:C15=C17))
=SUMIF(C2:C15,C17,D2:D15)

Blessings!

[63falcondude]

Why AGGREGATE over SUMIF?

You can use this:

=SUMIF(C2:C15,C17,D2:D15)

[hemantparmar]

Thanks guys, I was just trying to use Aggregate as single formula to do multiple other things with all arguments as variables. Forgot about the Array part for functions. Now I am clear.