# SUMIFS date range general mistake

Copper Contributor

# SUMIFS date range general mistake

Good morning,

In my excel I'm trying to sum data by date range:

=SUMIFS(\$H\$2:\$H\$300;\$E\$2:\$E\$300;">"&M2;\$E\$2:\$E\$300;"<"&M3)

And get "You have mistake in your formula" with the whole line highlited!

The E range and M2, M3 cells are Date.

Besides SUMIF - works perfectly with the same syntaxis:

=SUMIF(\$E\$2:\$E\$300;">"&M2;\$H\$2:\$H\$300)

The question - Why? SOLVED! Some kind of a bug. After reboot it works.

11 Replies

# Re: SUMIFS date range general mistake

SUMIFS() does not sort.

Remove ;TRUE;0 from your formula. It makes no sense.

# Re: SUMIFS date range general mistake

It's late here, I meant Sum, and the thing is not in TRUE;0 issue. Thanks for useless help :)

# Re: SUMIFS date range general mistake

The formula should have commas instead of semicolons, and the ranges and criteria should be properly paired.

=SUMIFS(\$H\$2:\$H\$300, \$E\$2:\$E\$300, ">"&M2, \$E\$2:\$E\$300, "<"&M3)

# Re: SUMIFS date range general mistake

Such mistake means something is wrong with syntax.

In general nothing is wrong with the formula

# Re: SUMIFS date range general mistake

@NikolinoDE thanks, but coma thing is regional isuue, we use ";" instead.

# Re: SUMIFS date range general mistake

@Sergei Baklan thats what i'm saying. Thanks. I use sumifs all the time and it works properly, but somehow does not want to recognise ">"& construction and sugests it as a mistake. Thus sumif is ok with it :(

# Re: SUMIFS date range general mistake

Have no idea what it could be, especially if is formula works after the reboot.  As workaround you may use

``=SUMPRODUCT( \$H\$2:\$H\$300*(\$E\$2:\$E\$300 > M2)*(\$E\$2:\$E\$300 < M3) )``

By the way, if change criteria places

``=SUMIFS(\$H\$2:\$H\$300, \$E\$2:\$E\$300,"<" & M3, \$E\$2:\$E\$300, ">" & M2)``

it also gives an error?

# Re: SUMIFS date range general mistake

If you are still encountering issues, there might be a problem with the date format or some other regional setting affecting the formula.

Ensure that the date format in cells M2 and M3 matches the date format in the range \$E\$2:\$E\$300.

If there are discrepancies, Excel may have trouble interpreting the date criteria.

Also, make sure that the cell references and data in the specified ranges are accurate and free from any extra spaces or characters that might cause issues.

# Re: SUMIFS date range general mistake

If data is wrong formula will return wrong result or #VALUE error, other words it'll be evaluated.

In this case alert "You have mistake in your formula" appears before evaluating, Excel can't recognize formula itself.

# Re: SUMIFS date range general mistake

Thanks mate. As I added to the topic - it was kind of general BUG. I rebooted, rewrote formula absolutely identically and it worked.

# Re: SUMIFS date range general mistake

My only guess if you had some non-printable character or wrong apostrophe sign within initial formula. Non-printable could appear if the formula copy/pasted from somewhere and partial editing didn't remove such character.