IF and

[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!

 

[Guest]

=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

 

[Guest]

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?

 

[Guest]

Oh and why is the IF AND not working?
Sorry I'm anal...

 

[Guest]

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

 

[Guest]

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)

 

[T. Valko]

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))

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.

 

[Guest]

Thanks much for your time!

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

 

[Guest]

If you use XL-2007, you can use SUMIFS function.

 

[Guest]

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.

 

[Guest]

Nevermind, it did work. Must have done something wrong the 1st time.

 

[Gord Dibben]

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

 

[Daan Vink]

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!

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