How to throw an Error deliberately

ygoldgrab

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!

Toufong Vang

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

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.

Toufong Vang

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

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

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

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