Aggregate Function

[cshetty]

Dear Experts

What wrong am doing with this formula.

=AGGREGATE(9,7,K6:K1022/(K6:K1022="Active"),L6:L1022)

Wanted to sum L6 to L1022 excluding errors for range K6 to K1022 equal to active. Is it possible using aggregate ?

Thanks in advance.

 

[Peter_SSs]

Try

=SUMPRODUCT(--(K6:K1022="Active"),IFERROR(L6:L1022,0))

 

[cshetty]

Try

=SUMPRODUCT(--(K6:K1022="Active"),IFERROR(L6:L1022,0))

Thank you Peter

Your code worked as an array formula.

Any non array formula alternate please.

 

[Peter_SSs]

Your code worked as an array formula.

Not sure what you mean? SUMPRODUCT function does not require Ctrl+Shift+Enter confirmation.

Any non array formula alternate please.

You are working with arrays of values so whatever you use has to be able to deal with arrays whether it requires c+s+e or not.

 

[Peter_SSs]

SUMPRODUCT function does not require Ctrl+Shift+Enter confirmation.

Hmm, I don't have an old version to check but perhaps the use of IFERROR might make the formula require c+s+e?
If that is the case then I think that you would need an array-entered formula.