IFERROR(SUMIFS returns 0 and not the alternate formula?

Hi,

I have this formula but since 0 is a number and not an error it is returning 0's instead of my error furmula. What would be the best way to go about fixing this?

=IFERROR(SUMIFS(Billed!$D:$D,Billed!$A:$A,F72,Billed!$B:$B,$B72),IF(F74="Internal",(F76/(1+15%)),(F76/(1+F73+6%))))

.

Taking a stab here - formulas are not my forte' :

Please Login or Register  to view this content.

Note the addition at the end of the formula.

Originally Posted by Logit

.

Taking a stab here - formulas are not my forte' :

Please Login or Register  to view this content.

Note the addition at the end of the formula.

This wont paste correctly said too many arguments.

Originally Posted by TMS

You don't need all the brackets

Formula:

Please Login or Register  to view this content.

However, the only way I can see to get an error in the first part of the formula is if F72 or B72 generate an error, for example, #VALUE!

This one cut back on the number of brackets but didnt actually solve the issue of returning 0.

A little late on my part. I have attached a sample file. F16 hold the desired result of H16. G16 is my attempt at a formula, both F16 and G16 are correct answeres the difference being that when i try to combine the if statement into an iferror statement as done in cell G16 and H16 the error part does not work because a sum of $0 is not actually an error.

Thanks,
Mike

The problem is exactly as you initially stated yourself in the original post.
The result of 0 from sumifs is NOT an error, therefore IFERROR will never do anything with it.

Try
=IFERROR(1/(1/SUMIFS(Billed!$D:$D,Billed!$A:$A,F72,Billed!$B:$B,$B72)),IF(F74="Internal",(F76/(1+15%)),(F76/(1+F73+6%))))

So both of these work and i thank you both for your help. Is any one of these better than the other if i had to use multiple statements to look up multiple item ids? i.e 611, 118, 356 for sum values?

Originally Posted by TMS

If you want to execute the "IFERROR" part of the formula if the SUMIFS is zero, you need to test the SUMIFS output for a zero return.


Comme ci

Formula:

Please Login or Register  to view this content.

Originally Posted by Jonmo1

The problem is exactly as you initially stated yourself in the original post.
The result of 0 from sumifs is NOT an error, therefore IFERROR will never do anything with it.

Try
=IFERROR(1/(1/SUMIFS(Billed!$D:$D,Billed!$A:$A,F72,Billed!$B:$B,$B72)),IF(F74="Internal",(F76/(1+15%)),(F76/(1+F73+6%))))

In the first one, you're doing the sumifs twice.
Once to test if it's 0, then again to return it's value when it's not 0.

Mine should be more efficient.
It's applying just a little math to the result of sumif which basically forces a 0 into an error therefore allowing iferror to trap it.