Aggregate and Products of Arrays

[ryjmc]

It seems strange to me that AGGREGATE doesn't return the expected result when using the product of two arrays. I have the following example:

=AGGREGATE(9,6,(F26:F880)*NOT(ISERROR(H26:H880)))

where cols F and H have values and errors. I simply wanted to get the sum of the values in F ignoring those values in F where F is an error, or the corresponding value in H is an error. When I evaluate the formula, it appears to in fact produce the expected array in the second to last step, but then end up evaluating to #VALUE!. Am I missing something here, or has this functionality, just been left out of the coding for the AGGREGATE function.

It appears that SUMIFS does work:

=SUMIFS((F26:F880),(F26:F880),">0",(H26:H880),">0")

Thank you for any insight.

 

[Domenic]

In your AGGREGATE formula, this part returns an array...

(F26:F880)*NOT(ISERROR(H26:H880)

However, only certain functions (ie. small, large, etc.) accept arrays. Hence the error value. See Excel Help for additional information.

 

[ryjmc]

Domenic,

Thanks for the confirmation. Seems like providing array handling for all of the options of AGGREGATE would have made a lot of sense. I wonder why it wasn't coded for that.

Cheers

In your AGGREGATE formula, this part returns an array...

(F26:F880)*NOT(ISERROR(H26:H880)

However, only certain functions (ie. small, large, etc.) accept arrays. Hence the error value. See Excel Help for additional information.