Search code examples
excelexcel-formulaaggregate-functionssumifs

AGGREGATE function not ignoring errors when it should

I need an aggregate function to sum a column depending on what's on the next column. I only want to sum values in front of cells formatted as dates (2nd and 4th rows) which correspond to February only (2nd row).

Here's how the table looks :

Sum (1) | Date (2)

40      February
29,05   19/02/2019
0,00    February
10,00   23/03/2019
10,00   February
17,65   March
0,00    February

I found a way to do this, but this is an array formula, and I have to avoid to enter it with the Control-Shift-Enter key combination.

Here is the working formula I have now (I found part of this formula here: Excel Formula to SUMIF date falls in particular month)

={SUM(IFERROR(IF(MONTH(February_expenses[Date])=2;February_expenses[Sum];0);0))}

This gives the 29.05 expected result. Now, here is what I have for the aggregate function (code 9 is for "sum", code 6 is for "ignoring errors") :

=AGGREGATE(9;6;February_expenses[Sum]/(MONTH(February_expenses[Date])=2))

Which also translates to (highlight + F9 key) :

=AGGREGATE(9;6;{#VALUE!;29,05;#VALUE!;#DIV/0!;#VALUE!;#VALUE!;#VALUE!})

The problem is, this function returns "#VALUE!". I can't figure out why because I indicated the function to ignore errors (code 6). Any idea where I may be wrong ?

Solution

  • The Reference forms of the AGGREGATE function doesn't accept arrays as arguments (unless the array is a range of cells). And the Array forms don't include a SUM function.

    If you want to avoid having to use CSE, you can use SUMPRODUCT like this:

    =SUMPRODUCT(ISNUMBER(1/(MONTH(February_Expenses[Date])=2))*February_Expenses[Sum])

    The 1/(…) converts the FALSE returns to an error to eliminate the non-February months in addition to the real error values.