IF and
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 and
=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 and
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! |
IF and
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 and
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 and
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! |
IF and
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! |
IF and
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 and
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! |
IF and
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! |
IF and
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! |
IF and
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! |
Quote:
=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. |
All times are GMT +1. The time now is 06:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com