Array formula: returning non-zero for negative search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an array formula: =SUM(IF(C2:C9="Apples",IF(I2:I9>=0,I2:I9,0),0)) where C2:C9 is product, H2:H9 is delivery date and I2:I9 is period since delivery. This works well where there are apples in C2:C9, but delivers zero when there are none, which causes problems in the subsequent formula. How do I get "24" as a result when there are no apples in C2:C9
I am a newbie, hope you can help
David
 
I think in row 2 for example Q2 write : "=if(exact
(C2;"apples");I2;0) and Enter .Then when your mouse is in
the right bottom of cell Q2 a + mark appears .drag it
down .then Write in one cell :"=sum(Q2:Q9)
-----Original Message-----
I have an array formula: =SUM(IF(C2:C9="Apples",IF
Click to expand...
(I2:I9>=0,I2:I9,0),0)) where C2:C9 is product, H2:H9 is
delivery date and I2:I9 is period since delivery. This
works well where there are apples in C2:C9, but delivers
zero when there are none, which causes problems in the
subsequent formula. How do I get "24" as a result when
there are no apples in C2:C9?
 
Thanks for suggestion. I have followed this but zero still present when no apples in C2:C9. Where should I put 24? Is there anything else I should do
David
 
First, you could use another formula that may be easier:

=SUMPRODUCT(--(C2:C9="apples"),--(I2:I9>=0),i2:i9)
This isn't array entered.

This (C2:c9="apples") returns an array of true/falses.
The first minus sign converts those trues to -1 and falses to -0 (just 0).
the second minus sign converts the -1's to +1's.
(The product part likes numbers--not booleans. So this converts them.)

The same kind of the thing to check the I2:i9 values (>=0).

Then the product part of sumproduct multiplies the +/- 1's by the value in
I2:i9.

Then the Sum part of sumproduct adds it up.

And you want 24 if there are no "apples" in c2:c9?

=if(countif(c2:c9,"apples")=0,24, yourformula)
(still array entered)
or
=if(countif(c2:c9,"apples")=0,24, myformula)
(still not array entered)
 
Back
Top