Nesting with #DIV/0!

[Celticshadow]

Hi All

I have the below formula which sums percentages in a row but ignores the #DIV/0!. I would like to know how if one of the percentages in the row exceeds say 100% how would I subtract that from the formula.

This is how the formula presently looks as below:

=SUMIF(A1:A11,"<1E100")

Which is summing the below figures to a total of 319%

71% 45% 140% 63%

As there is a percentage above 100% in the row (ie140%) I require to minus that figure from the total and I am unsure how to that within the above formula. Any help would much appreciated, I apologise as I should have included this in my original thread (Summing Ignoring #DIV/0!).

Thanks in advance.

Kind Regards










<table border="0" cellpadding="0" cellspacing="0" width="256"><col span="4" width="64"><tr height="17"> <td class="xl22" style="height:12.75pt;width:48pt" height="17" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> </tr></table>

 

[Tuelor]

I would add an if statement in column B to check if its over 100%, then sumif if they're not.

=SUMIF(B1:B11,FALSE,A1:A11)

D

 

[Celticshadow]

Hi Tuelor

First of all thanks for replying.

I am not sure I undertand that as when I enter it in to B (which contains a 100% fig) all I get is a zero. Is it possible to nest it in some way.

Kind Regards

 

[Tuelor]

Ok - try...

=SUMPRODUCT(A1:A11,IF(A1:A11<100,1,0))

..but make sure you enter it as a formula array (ie after entering the forumla press ctrl+shift+enter ands not the normal enter)

 

[T. Valko]

Hi All

I have the below formula which sums percentages in a row but ignores the #DIV/0!. I would like to know how if one of the percentages in the row exceeds say 100% how would I subtract that from the formula.

This is how the formula presently looks as below:

=SUMIF(A1:A11,"<1E100")

Which is summing the below figures to a total of 319%

71% 45% 140% 63%

As there is a percentage above 100% in the row (ie140%) I require to minus that figure from the total and I am unsure how to that within the above formula. Any help would much appreciated, I apologise as I should have included this in my original thread (Summing Ignoring #DIV/0!).

Thanks in advance.

Kind Regards

Just add another SUMIF to the end like this...

=SUMIF(A1:A11,"<1E100")-SUMIF(A1:A11,">1")

Or, we can get fancy and use something overly complicated like this...

=SUM(SUMIF(A1:A11,{"<1E100",">1"})*{1,-1})

 

[T. Valko]

Or, this which is probably the best option...

=SUMIF(A1:A11,"<=1")

Assuming you want to include values that are 100%.