Aggregate Function with Criteria

[Tikey]

I am using Excel 2016 not 365.

I have been using the following formula to find the largest ( max ) value from a range of cells which are within a particular year. =AGGREGATE(14,6,Records!$I$2:$K$40000/(YEAR(Records!$F$2:$F$40000)=1992),1)

I now wish to SUM or COUNT the values from the same range, again with a particular year. I have tried modifying the formula as in the 2 attempts below but get a #VALUE ! error for both.

=AGGREGATE(9,6,Records!$I$2:$K$40000*(YEAR(Records!$F$2:$F$40000)=1992))
=AGGREGATE(9,6,Records!$I$2:$K$40000/(YEAR(Records!$F:$F)=1992))

Any help on this would be much appreciated.

 

[Aladin Akyurek]

Why don't you try the following?

=SUMPRODUCT(Records!$I$2:$K$40000*(YEAR(Records!$F$2:$F$40000)=1992))

 

[Tikey]

I got fixed on trying to get Aggregate to work, never thought about Sumproduct. It worked fine, thanks very much.
For my understanding what is the reason you chose to use that formula, or is it that Aggregate just wasn't suitable ?

Thanks again.

 

[RoryA]

Function 9 in aggregate won't work with an array, only a range.

 

[Aladin Akyurek]

I got fixed on trying to get Aggregate to work, never thought about Sumproduct. It worked fine, thanks very much.
For my understanding what is the reason you chose to use that formula, or is it that Aggregate just wasn't suitable ?

Thanks again.

You are welcome. Indeed not suitable for 9 (the sum function), as Rory word, does not process array objects while ordinary SUM would.