How to throw an Error deliberately

Options

Hi,

Is there a way to deliberately throw an error in a SumIF formula ?

I am calculating columns of Balance Due = Cost * Quantity but if Quantity is blank it returns zero or "No Qty", but I want it to throw an error so I when I sum up total Balance Due I'll know that I'm missing information.

Thanks!

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/20/21 Answer ✓
    Options

    If I understood you correctly...

    Your SUMIF() formula will fail when there is an error in the range you're trying to sum.

    The formula Balance Due = Cost * Quantity * (Quantity/Quantity) will cause a division-by-zero error when Quantity is blank or zero. This error in the range you're trying to sum will cause your SUMIF() formula to fail.

    Alternatively, your SUMIF() criterion could be @cell/@cell = 1. E.g., Total Balance Due = SUMIF([Balance Due]:[Balance Due], @cell/@cell = 1).

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    I'm not sure I fully understand.

    When the SUMIF() statement errors out, the result in the cell is the error message.

    If you want a sum that ignores "0", "No Qty", and blanks in the range, then the formula to use would be,

    Total Balance Due = SUMIF({Range1 Balance Due}, NOT(OR(@cell=0, @cell="No Qty", @cell="")))

    However, you'll not get an error message.

    If you want both (1) to know when there is a Blank or a "0" or a "No Qty"; and (2) the sum of non-zero numbers in the range; then you'll want a helper column or cell. Put the formula that causes a division-by-zero error in the helper column and the other formula in the Total Balance Due column or cell.

    For housekeeping, I would change the division-by-zero formula to...

    Helper_Column = IFERROR( SUMIF({range:range}, @cell/@cell = 1), "Missing Quantity or Some_Other_Txt")

    It would help if you share an annotated screenshot of what you're trying to do.

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/20/21 Answer ✓
    Options

    If I understood you correctly...

    Your SUMIF() formula will fail when there is an error in the range you're trying to sum.

    The formula Balance Due = Cost * Quantity * (Quantity/Quantity) will cause a division-by-zero error when Quantity is blank or zero. This error in the range you're trying to sum will cause your SUMIF() formula to fail.

    Alternatively, your SUMIF() criterion could be @cell/@cell = 1. E.g., Total Balance Due = SUMIF([Balance Due]:[Balance Due], @cell/@cell = 1).

  • ygoldgrab
    ygoldgrab ✭✭✭✭✭
    Options

    Thank you, however I'm referencing this column from another sheet so i would want to use your second idea but I cant figure out how to put in a conditin when summing a range on a different sheet

    Total Balance Due = SUMIF({range:range}, @cell/@cell = 1, {sumrange:sumrange}) just sums all numbers and ignores the blank values... Any ideas?

    Many thanks.

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    I'm not sure I fully understand.

    When the SUMIF() statement errors out, the result in the cell is the error message.

    If you want a sum that ignores "0", "No Qty", and blanks in the range, then the formula to use would be,

    Total Balance Due = SUMIF({Range1 Balance Due}, NOT(OR(@cell=0, @cell="No Qty", @cell="")))

    However, you'll not get an error message.

    If you want both (1) to know when there is a Blank or a "0" or a "No Qty"; and (2) the sum of non-zero numbers in the range; then you'll want a helper column or cell. Put the formula that causes a division-by-zero error in the helper column and the other formula in the Total Balance Due column or cell.

    For housekeeping, I would change the division-by-zero formula to...

    Helper_Column = IFERROR( SUMIF({range:range}, @cell/@cell = 1), "Missing Quantity or Some_Other_Txt")

    It would help if you share an annotated screenshot of what you're trying to do.

  • ygoldgrab
    ygoldgrab ✭✭✭✭✭
    Options

    Thanks for your help.

    I basically used your idea and set up a countif to count all the cells that had " NO QTY" so I would know how much data was missing.

    Thanks

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!