IF and

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

Guest

I want to sum amounts from one column if 2 other columns meet the criteria I
defined.
Here is what I have: =IF(AND(A:A="x",B:B=""),SUM(C:C))
Even though I have a lot of rows that match both criteria (have an "x" in
column A and nothing in column B), the formula returns "false".
Any ideas? Thanks!
 
=SUMPRODUCT(--(A1:A100="X"),--(B1:B100=""),C1:C100)
you can not use whole columns (eg. A:A, B:B, or C:C) unless you are using
XL-2007
 
Thanks, it works! I am using 2007.
I wish I understood the "(--(" bit. I keep seeing it in the posts but don't
get what it's for.
Any light on this?
 
You can not use "AND" function in an arrray formula. Use "*" isnstead of "AND"

=SUM(IF((A:A="x")*(B:B=""),C:C))
ctrl+shift+enter, not just enter
or
=SUM((A:A="x")*(B:B="")*C:C)
Also ctrl+shift+enter, not just enter
 
Double unary "--" it converts TRUE/FALSE into 1/0
eg.
=--(condition1)
if condition 1 is TRUE, it returns a "1" (no quote)
if condiion 1 is FALSE, it returns a "0" (no quote)
 
You can not use "AND" function in an arrray formula.

Sure you can. Array entered:

=AND(A1:A10="x")

If any cell does not = "x" then the result is FALSE.

That is the same as:

=COUNTIF(A1:A10,"x")=10
=IF(AND(A:A="x",B:B=""),SUM(C:C))
Click to expand...

If *every* cell in A = "x" and *every*cell in B = "" then that formula would
work (if array entered).

Obviously, that's not how the OP intended it to work.
 
Thanks much for your time!

Teethless mama said:
You can not use "AND" function in an arrray formula. Use "*" isnstead of "AND"

=SUM(IF((A:A="x")*(B:B=""),C:C))
ctrl+shift+enter, not just enter
or
=SUM((A:A="x")*(B:B="")*C:C)
Also ctrl+shift+enter, not just enter
Click to expand...
 
I actually tried that one, SUMIFS, and all my arguments were correct
according to the window that opens up when you hit the fx button, but it
returned 0.
 
That is the result you see when the numbers are text.

Format all to General then copy an empty cell.

Select the range of data and Paste Special(in place)>Add>OK>Esc.


Gord Dibben MS Excel MVP
 
Caroline;554998 said:
I want to sum amounts from one column if 2 other columns meet th
criteria I
defined.
Here is what I have: =IF(AND(A:A="x",B:B=""),SUM(C:C))
Even though I have a lot of rows that match both criteria (have an "x
in
column A and nothing in column B), the formula returns "false".
Any ideas? Thanks!
Click to expand...

Try this, for instance in cell D1:
=SUM((A1:A10="x")*(B1:B10="")*(C1:C10))
but don't finish with Enter but with Ctrl+Shift+Enter (cos it's a
array formula). If you entered the formula correcty, you should see {
} around the formula. You can't type the { } however.
Regards, Daan
 
Back
Top