# Sumif and Sumifs with open ranges gives #ERROR in one sheet, correct result in other

Here's my test sheet:

Localization is USA with timezone GMT+1 I've tried substituting ',' with ';' to no avail.

As you can see, sumif/sumifs with open ranges returns #ERROR.

``````=sumifs(!b2:b,!a1:a,">=2/1/2018",!a1:a,"<=2/2/2018")
``````

I know it should work because I've done `sumifs` with open ranges before and worked as intended.

Can someone help?

• Welcome. While I was taking a look into your spreadsheet some else was editing so I can't see the formula that was causing the error. To prevent this situations, please share your spreadsheet to anyone with the link can view only and include the formula in the question as the spreadsheet could not be accesible in the future. – Rubén Oct 12 '19 at 20:04
• thanks, will do – vaitrafra Oct 13 '19 at 12:35

First formula :

Don't use `=!A1:A` or `=!A1` these are not ranges or cell addresses. The operator `!` is used to reference a range in a specific tab like this : `=Sheet1!A1`

Using `=A:A` the `sumif` works.
In E3 : `=sumif(A:A,">=2/1/2018",B:B)`

Second formula error : `Argument must be a range.` It's because filtering `B:B` breaks the formula in the third argument of `=sumif`.

Try this in `E4` instead :

``````=sumif(filter(A:A,A:A<>"",B:B<>0),">=2/1/2018",B:B)
``````

Same deal for `sumifs`. `F4` is now :

``````=sumifs(B:B,B:B,"<>0",A:A,"<>",A:A,">=2/1/2018",A:A,"<=2/2/2018")
``````
• This noob thanks you a lot. – vaitrafra Oct 13 '19 at 12:41