Google Sheets – AVERAGEIFS Parse Error

formulasgoogle sheets

=AVERAGEIFS(Sheet12!R:R,Sheet12!A:A,N5,Sheet12!B:B,">=9/16/2018",Sheet12!B:B,"<=10/3/2018")

While giving cell range instead of a date value, it's showing error.

Best Answer

=AVERAGEIFS(Sheet12!R:R, Sheet12!A:A, N5,
                         Sheet12!B:B, ">="&DATE(2018, 9, 16),
                         Sheet12!B:B, "<="&DATE(2018, 10, 3))

Related Solutions

Google-sheets – Error with exact date 21/02/1971 – function MINUS expects number values

For lack of detail in the Q this is pure speculation:

Guess #1: There is nothing whatever special about 21/02/1971.
Guess #2: It was imported from a web site.
Guess #3: The cell in question contains a non-printing character.
Guess #4: That character is Unicode, not ASCII – so would not be removed with =CLEAN.
Guess #5: That character is ZWSP.
Guess #6: It is at the end of the date only.

If so, where A1 is the reference to the cell containing that date:

Guess #7:

=left(A1,10)

might suit, prior to subtraction from =TODAY().

Google-sheets – Arrayformula with in column operations

Formula

=ArrayFormula(Array_constrain(A2:A-A:A,COUNT(A:A)-1,1))

Explanation

Use Array_constrain to avoid filling the last row and filling up blank rows.