EXCEL FORMULA - IF STATEMENT

I don't see how H6:H7 in this case will evaluate to either 0 or "". H6:H7 is a cell range and a parameter to a function, by itself it doesn't evaluate to anything.

what do you want to do here?
formula does not make sense to me...

just say =sum(h6:h7) it will ignore non numeric values like "x 1" and sum the rest...

Agree with HainKurt, it would probably be best if you tell us what you want achieve.

Hi there, I dont understand what the confusion is? First of all the value should = "x 1" for the cell range shown on my example above.
I dont want to just do "=sum(h6:h7)" what I want is for this cell to show nothing if either a zero value or nothing is in that cell range.

the range H6:H7 will not evaluate to "x 1" - a cell range by itself won't evaluate to anything.  A cell range is a parameter to a function, not something can be evaluated by itself.

For instance in your example "=sum(h6:h7)" evaluates to 0 (it ignores the non-number in H6 and interprets the empty cell in h7 as 0 and thus evaluates to 0).  However =h6:h7 doesn't evaluate to anything, as that is not what cell ranges do.

I have this array formula which does what I understand from your description but I am not convinced that this is what you are looking for.

=INDEX(H:H,MIN(IF((H6:H7=0)*(H6:H7=""),FALSE,ROW(H6:H7))),1)

Array formulas are entered by pressing ctrl-shift-enter. The correctness of entry is verified by braces {} around the formulas shown in the formula bar. If you do not see the braces then
Select the formula cell
press F2
press Ctrl-shift-enter

>>I dont want to just do "=sum(h6:h7)" what I want is for this cell to show nothing if either a zero value or nothing is in that cell range.

@FrankSasso - YOU WERE VERY CLOSE

If your formula was ARRAY ENTERED, it would work nicely.  Just edit the formula (hit F2) and then hit CTRL+SHIFT+ENTER.  You'll see curly braces appear around the formula, like this:

[H4]{=IF(OR(H6:H7=0,H6:H7=""),"",SUM(H6:H7))} - don't type the curly braces, they're put there by Excel.

As an alternative, you could also use this formula in cell H4

[H4]=IF(OR(H6=0,H7=0,H6="",H7=""),"",SUM(H6:H7))

This alternative is NOT an array formula, and doesn't require the special key sequence to convert it.  It probably is more efficient, as a result.

Cheers,

Dave

@ssaqibh - do you sleep?  I guess I need to use less words, as I spent probably 10 minutes composing, lol ;P  Didn't know you'd posted

;)

Dave

Hi Dave, thankyou for your reply.
I need this to work on an array/ range of cells so there must be a solution for this problem I have? Would a "sumproduct" with an if statement work?

FrankSasso - it does work - edit the formula and hit CTRL+SHIFT+ENTER

I tested it.  If H6 or H7 are 0 OR H6 or H7 are blank, the result is to leave it blank.

Yes, you can do sumproduct as well.  First, try the CTRL+SHIFT+ENTER approach.  You built it and if successful, it will stay with you longer ;)

See attached,

Dave
demoformula-r1.xls

Simpler, and perhaps easier to understand/debug in future, would be just to use COUNTIF (and there are probably many other ways as ssaqibh demonstrated):

This is not an array formula, so doesn't have to be array entered

=IF(OR(COUNTIF(H6:H7,"")>0,COUNTIF(H6:H7,0)>0),"",SUM(H6:H7))

The >0 doesn't HAVE to be entered, but we do it for clarity (my preference, anyway)

see attached

Dave
demoformula-r2.xls

Finally - SUMPRODUCT because you specifically asked:

=IF(SUMPRODUCT((H6:H7="")+(H6:H7=0))>0,"",SUM(H6:H7))

I think COUNTIF in this case more efficient, but you can add additional criteria, etc., with the SUMPRODUCT.

Cheers,

Dave
demoformula-r3.xls

Hi dlmille,
thankyou for your reply, i found with your demo that when i deleted one of the qtys the return value showed nothing which isnt correct, should have shown 1.
Also, I have tried the "F2" and then the CTRL+SHIFT+ENTER

Hi Dlmille, I have tried your suggestion please see below but still it does not work?

All three formulas I gave you evaluate the same way, starting with your formula which would be array entered.

>>i found with your demo that when i deleted one of the qtys the return value showed nothing which isnt correct, should have shown 1.

You say this, but before you said:

I dont want to just do "=sum(h6:h7)" what I want is for this cell to show nothing if either a zero value or nothing is in that cell range.

The formulas evaluate based on what you said - if H6 OR if H7 are EITHER zero or are EITHER blank (nothing) then the formula evaluates to blank (nothing).

Are you changing your criteria?

Dave

Perhaps I interpreted that incorrectly?

If H6 OR H7 is blank, OR if H6 OR H7 is "", then you DO want to sum?

Let me know?

Dave

If either H6 or H7 is blank

or

either H6 or H7 has a zero

Then SUM H6:H7

Here are the 3 formulas we discussed if this is what you're trying to do:

1.  The array entered formula:  =IF(OR(H6:H7="",H6:H7=0),SUM(H6:H7),"")
2.  The countif formula: =IF(OR(COUNTIF(H6:H7,"")>0,COUNTIF(H6:H7,0)>0),SUM(H6:H7),"")
3.  The sumproduct formula: =IF(SUMPRODUCT((H6:H7="")+(H6:H7=0))>0,SUM(H6:H7),"")

This appears to be a reversal of logic, but perhaps lost in the interpretation on my end.

The attached example file demonstrates these formula changes.

Let me know - I think we've covered the universe of possibilities with this example, ;)

Dave
demoformula-r4.xls

Hi Dave, I have not changed my criteria. Basically what I want in case its still not clear is, cell H8 will always have to sum cell array of H6:H7, the only difference is that if array H6:H7 has either no values or has the value of 0 (zero) then the value of H8 is nothing.
Is this clear enough?

Let's do an example to ensure I'm clear, because I think I gave you that formula that does that, initially:

H6=0
H7=1
H8="" <- result nothing
-------
H6=""
H7=1
H8="" <- result nothing
------
H6=1
H7=5
H8=6 <-result do the sum

Is this what you're looking for?  Are my answers correct?

Dave

Let me try with option 2 as perhaps I'm seeing more sophistication than what you're after...
----------------------------------
This will provide a sum if there are values in H6 or H7, except blank or zero.

You want H8 to be nothing unless there's something in H6:H7 to add?

[H8]=IF(SUM(H6:H7)=0,"",SUM(H6:H7))

examples, below:

H6=blank or zero
H7=blank or zero
H8=NOTHING
-----
H6=a value OR
H7=a value
H8=sum of the values
-----
H6=1
H7=2
H8=3

Is this correct, now?

The reason for all the confusion is what you're original formula suggested, which led my brain in that direction.

Dave

Hi Dave, no your answers are not correct, please follow.
Im not sure why this has become so misinterpreted?

H6=""
H7=0
H8=""

H6=0
H7=1
H8=1

H6=0
H7=0
H8=""

H6=""
H7=""
H8=""

H6=1
H7=1
H8=2

thankyou, thankyou!

I'm so glad you got what you needed.  That was a bit painful for all of us.  I'll take the hit, I should have asked for the example to see if the math was correct earlier in the question.

Next time!

Peace.

Dave