 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007IF 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! =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 > 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? > =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 [quoted text clipped - 6 lines] > > 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) > Thanks, it works! I am using 2007. > I wish I understood the "(--(" bit. I keep seeing it in the posts but don't [quoted text clipped - 11 lines] > > > 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... > =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 [quoted text clipped - 6 lines] > > 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 > Oh and why is the IF AND not working? > Sorry I'm anal... [quoted text clipped - 9 lines] > > > 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.  SignatureBiff Microsoft Excel MVP > You can not use "AND" function in an arrray formula. Use "*" isnstead of > "AND" [quoted text clipped - 21 lines] >> > > column A and nothing in column B), the formula returns "false". >> > > Any ideas? Thanks! Thanks much for your time! > You can not use "AND" function in an arrray formula. Use "*" isnstead of "AND" > [quoted text clipped - 17 lines] > > > > column A and nothing in column B), the formula returns "false". > > > > Any ideas? Thanks! If you use XL-2007, you can use SUMIFS function. > 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. > If you use XL-2007, you can use SUMIFS function. > [quoted text clipped - 4 lines] > > 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)>Add>OK>Esc. Gord Dibben MS Excel MVP >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 [quoted text clipped - 8 lines] >> > 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. > If you use XL-2007, you can use SUMIFS function. > [quoted text clipped - 4 lines] > > column A and nothing in column B), the formula returns "false". > > Any ideas? Thanks! Caroline;554998 Wrote: > I want to sum amounts from one column if 2 other columns meet th > criteria I [quoted text clipped - 4 lines] > 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 -- Daan Vink |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.