Learn to use the Aggregate Function

Here is a great link to learn about this powerful excel function

AGGREGATE

https://exceloffthegrid.com/aggregat...ure-not-using/

FWIW, I find AGGREGATE's ability to skip error values as problematic as IFERROR. There can be good reasons to skip all #N/A and #VALUE! errors, also many but not all #DIV/0! and #NUM! errors, but there's almost never a good reason NOT to propagate #REF!, #NULL! and #NAME? errors, and in the latest Excel also #CALC! and #SPILL!. If only it were easier to specify which error values to skip and which to propagate than using (latest Excel) constructs like

LET(x,some_arcane_expression,IFERROR(IF(AND(ERROR.TYPE(x)<>{3,7}),x),x))

which returns nonerrors, FALSE for #N/A and #VALUE!, and all other error values as-is.

Why is this distinction needed? Because #N/A is a proxy for missing values. Excel needs a missing data pseudovalue. Also, it's an expected return value from unsuccessful lookups and MATCH calls. Then there's #VALUE! which can come from trying to use nonnumeric text in arithmetic operations or as an expected return value from unsuccessful FIND and SEARCH. To repeat, Excel REALLY needs a #MISSING! pseudovalue. #DIV/0! is an expected return value from trying to average ranges or arrays with no numeric values, and #NUM! is an expected return value for a few arithmetic operations involving negative numbers. Unfortunately, real #DIV/0! errors from explicitly dividing anything by 0 should propagate, and #NUM! errors due to actual underflow/overflow (and Excel's continuing inability to use hardware floating point for MOD) should do so also. All other errors SHOULD BE unexpected, so should never be trapped.