I want to avoid an error message on a SUMIFS function.

Wolfram

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!

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.