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

"Caroline" wrote:

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!

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?

"Teethless mama" wrote:

=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

"Caroline" wrote:

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!

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

"Teethless mama" wrote:

=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

"Caroline" wrote:

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!

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


Caroline" wrote:

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

"Teethless mama" wrote:

=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

"Caroline" wrote:

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!

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)


"Caroline" wrote:

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?

"Teethless mama" wrote:

=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

"Caroline" wrote:

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!

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.


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
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


Caroline" wrote:

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

"Teethless mama" wrote:

=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

"Caroline" wrote:

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!

Thanks much for your time!

"Teethless mama" wrote:

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


Caroline" wrote:

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

"Teethless mama" wrote:

=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

"Caroline" wrote:

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!

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

"Caroline" wrote:

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!

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.

"Teethless mama" wrote:

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

"Caroline" wrote:

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!

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

"Teethless mama" wrote:

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

"Caroline" wrote:

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!

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


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 12:18:00 -0700, Caroline
wrote:

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.

"Teethless mama" wrote:

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

"Caroline" wrote:

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!

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 an array formula). If you entered the formula correcty, you should see { } around the formula. You can't type the { } however.
Regards, Daan.