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!

Find more posts tagged with

Accepted answers

All comments

Paulo Ferrer

You can add one more condition in sumifs: like "is not blank" or greater than a referential date (01/01/1900). It should work.

Paulo Ferrer

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)

Wolfram

@Paulo Ferrer

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.

Chris Kleinheksel

=if(NOT(ISBLANK([date]@row)),SUMIFS(Amount:Amount, Date:Date, YEAR(@cell) = Year@row, Date:Date, MONTH(@cell) = CurrentMonth@row),"")

Wolfram

@Chris Kleinheksel

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.