How to throw an Error deliberately
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

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 divisionbyzero 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)
. 
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 nonzero numbers in the range; then you'll want a helper column or cell. Put the formula that causes a divisionbyzero error in the helper column and the other formula in the Total Balance Due column or cell.
For housekeeping, I would change the divisionbyzero 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

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 divisionbyzero 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)
. 
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.

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 nonzero numbers in the range; then you'll want a helper column or cell. Put the formula that causes a divisionbyzero error in the helper column and the other formula in the Total Balance Due column or cell.
For housekeeping, I would change the divisionbyzero 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.

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
Categories
Check out the Formula Handbook template!