2

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?

  • 1
    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 at 20:04
  • 1
    thanks, will do – vaitrafra Oct 13 at 12:35
1

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 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.