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

Options

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

  • Paulo Ferrer
    Paulo Ferrer ✭✭✭✭
    Options

    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
    Paulo Ferrer ✭✭✭✭
    Options

    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
    Wolfram ✭✭
    Options

    @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
    Options

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

  • Wolfram
    Wolfram ✭✭
    Options

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!