SumIf one range and another range do not = 0?

Posted by Shazadi on February 22, 2001 7:23 PM

Yes it is me again.
Thank you so much for your help earlier both Mark W and cpod, both your suggestions worked great.

Next difficulty:

=SUMIF(AB5:AB44,AB5:AC44>0)

I am trying to sum one column(AB) up, only if both Column AB and AC are greater than 0. And then will do the same for Column AC.

This formula above does not work and I have tried variations on it and they don't work either. Where am I going wrong?

Help! Help! Help! ...again

Thank You,
shaz

Posted by cpod on February 22, 2001 7:53 PM

Try this:

=SUM((AB1:AB12>0)*(AC1:AC12>0)*AB1:AB12)

This is an array function and must be entered using Control+Shift+Enter

Posted by David Hawley on February 23, 2001 12:27 AM

Hi Shaz

Here is another way:

=SUM(IF(AB5:AB44>0,IF(AC5:AC44>0,AB5:AC44)))

This too is an array and must be entered with Ctrl+Shift+Enter.

I have some more examples and important rules for arrays on my web site under the link "Array Formulas" that may interest you.

OzGrid Business Applications