I want to avoid an error message on a SUMIFS function.
Here is the function:
=SUMIFS(Amount:Amount, Date:Date, YEAR(@cell) = Year@row, Date:Date, MONTH(@cell) = CurrentMonth@row)
This works to tell me the "Amount" of a specific month and year.
The problem is I get an #INVALID DATA TYPE message if the cell in the Date column is not populated but the cell in the Amount column is.
If I were the only one using this grid it would be okay but I am not and I don't want to hunt for errors every time someone inputs data like this.
Thanks in advance!
Answers
-
You can add one more condition in sumifs: like "is not blank" or greater than a referential date (01/01/1900). It should work.
-
You can add one more condition in sumifs: like "is not blank" or greater than a referential date (01/01/1900). It should work.
Or, if the error is always of this type you can start with an IFERROR and indicating the desired condition (show zero for example)
-
Thanks for taking the time. The problem is it cannot show zero. It has to calculate the formula. Maybe I'm going about it wrong. Here is the problem:
This sheet is where I enter all the bills that we need to pay. They are sorted by the due date. I need to show how much is owed in a given month. I previously did this with a simple SUM function but the problem is that I need to keep adjusting the parameters as I am adding new bills. Thats how I came up with the above SUMIFS function. As I hinted at above, the problem is if someone enters a bill (i.e. a figure in the Amount column) but doesn't enter a due date, the function stops working. if I wrap my function in an IF or IFERROR function then I won't get an error message but I also won't get the correct figure.
I feel like there is an easy solution that I'm just not seeing. Again, thanks for your help.
-
=if(NOT(ISBLANK([date]@row)),SUMIFS(Amount:Amount, Date:Date, YEAR(@cell) = Year@row, Date:Date, MONTH(@cell) = CurrentMonth@row),"")
-
Hi, Thanks for that. The problem with your formula is that it only evaluates the date@row but I need it to evaluate the entire column. the other thing is that if it is blank, it will not calculate any of the other rows which would be wrong too.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 361 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!