Google Sheets – Fix #ERROR in Sumif and Sumifs with Open Ranges

google sheets

Here's my test sheet:

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

https://docs.google.com/spreadsheets/d/1pZku_X6b49uMhKivnv3N1uv6sekYrhY12IlUct_Zudc/edit?usp=sharing

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?

Best Answer

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")
Related Topic