Why excel is not working correctly when comparing date with "<=" operator?

Question

I'm using a sumif formula with condition that when the date is smaller or equal to the specific date, it will sum up the value. My formula:

I expect the answer will be summing up all values in column A but turns out only sum up the first row The result:

What is weird is that when I can the sign to "=", the formula can add up all the date in column B matching the dates but can't add up the number when I use "<=" operator

Answer 1

The DATEVALUE function is not needed. Just get remove it:

=SUMIFS($G:$G,$E:$E,"<="&$E2)

EDIT: In response to updated question

I believe the problem is how Excel is interpreting your dates. Any date comparisons will not work as expected if Excel is treating the value in the cell as text instead of Dates (or numbers).

To check this, you can add a temporary column with the formula:

=ISTEXT($E2)

Then drag down. If any return true, these are not being interpreted as dates by excel.

Here is an example. The "dates" highlighted in yellow are entered as text values.

To ensure the values ARE interpreted as dates, you can add another temporary column with this formula:

=$E2+0

Then copy the the temporary column into your date column "as values". You can do this by copying the temporary column, then right clicking and selecting this icon:

Ensure that your date column is formatted as "Short Date". Finally, you can delete any temporary columns.