 |
 | 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 / New Users / May 2008Sum Based on Exception
The results of the following formula is 0 when column G has values in it. =SUM(IF($B$6:$B$65536="John Doe",IF($B$6:$B$65536="Jane Dane",IF($B$6:$B$65536="Jack Dark",IF($G$6:$G$65536<>"",0,1))))) Can someone please show me what I am missing or don't need in the formula in order to get the right sum?  SignatureCue Can you elaberate more on what you are trying to accomplish? It look more like your trying to count how many names other then the ones listed there are. Help me understand what your trying to do. SignatureIf this reply was helpful, please indicate that your question has been answered to help others find anwsers to similar questions. Fighting Texas Aggie Class of 2009 > The results of the following formula is 0 when column G has values in it. > [quoted text clipped - 3 lines] > Can someone please show me what I am missing or don't need in the formula in > order to get the right sum? Oh. I'm trying to count the number of John Doe's, Jane Dane's & Jack Dark's in column B if column G has value of 'yes' or 'no'. SignatureCue > Can you elaberate more on what you are trying to accomplish? It look more > like your trying to count how many names other then the ones listed there [quoted text clipped - 7 lines] > > Can someone please show me what I am missing or don't need in the formula in > > order to get the right sum? Try this {=SUM(IF($G6:$G65536="no",0,IF($G6:$G65536="yes",IF($B6:$B65536="John Doe",1,IF($B6:$B65536="Jane Doe",1,IF($B6:$B65536="Jack Dark",1,0))))))} this is an array formular. Remember you need to enter it as an array by entering the formula and pressing Ctrl+Shift+Enter SignatureIf this reply was helpful, please indicate that your question has been answered to help others find anwsers to similar questions. www.silverbirddesigns.com Fighting Texas Aggie Class of 2009 > Oh. I'm trying to count the number of John Doe's, Jane Dane's & Jack Dark's > in column B if column G has value of 'yes' or 'no'. [quoted text clipped - 10 lines] > > > Can someone please show me what I am missing or don't need in the formula in > > > order to get the right sum? Thanks alot! SignatureCue > Try this > [quoted text clipped - 18 lines] > > > > Can someone please show me what I am missing or don't need in the formula in > > > > order to get the right sum? > .. I'm trying to count the number of John Doe's, Jane Dane's & Jack Dark's > in column B if column G has value of 'yes' or 'no'. Another option, normal ENTER will do: =SUMPRODUCT((ISNUMBER(MATCH(B6:B100,{"John Doe";"Jane Dane";"Jack Dark"},0)))*(ISNUMBER(MATCH(G6:G100,{"Yes";"No"},0)))) Adapt the ranges to suit. For good recalc performance, use the smallest range large enough to cover the max expected extent of your data (do you really need to go down to row 65k?) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Yea, that would work too. Didnt consider at the time. isnt there a way to check the entire column without specifing 65k. I know there is but cant think of it. SignatureFighting Texas Aggie Class of 2009 > > .. I'm trying to count the number of John Doe's, Jane Dane's & Jack Dark's > > in column B if column G has value of 'yes' or 'no'. [quoted text clipped - 6 lines] > range large enough to cover the max expected extent of your data (do you > really need to go down to row 65k?) > .. isnt there a way to check the entire column without specifing 65k. Guess you mean using entire col references, like: A:A, B:B etc? In xl03, my ver, sumproduct or array-entered sum will not work with entire col refs. But I've read from posts that xl07 allows that, which makes for simpler/shorter looking formulas all round. Albeit I'm not sure about the resultant recalc speeds in xl07 if entire col refs were used liberally in such formulas. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- I havent worked with xl07 yet, i bet that is what I am thinking about SignatureIf this reply was helpful, please indicate that your question has been answered to help others find anwsers to similar questions. Fighting Texas Aggie Class of 2009 > > .. isnt there a way to check the entire column without specifing 65k. > Guess you mean using entire col references, like: A:A, B:B etc? In xl03, my > ver, sumproduct or array-entered sum will not work with entire col refs. But > I've read from posts that xl07 allows that, which makes for simpler/shorter > looking formulas all round. Albeit I'm not sure about the resultant recalc > speeds in xl07 if entire col refs were used liberally in such formulas. > .. I havent worked with xl07 yet, i bet that is what I am thinking about Neither have I. Btw, a little bird also told me that in xl07, the sheet's extended to 1 million rows. Given this phenomenal increase (from 65k) I wonder how the resultant recalc speeds look like if there were extensive sumproduct/array-entered conditional sum expressions with entire col refs used liberally. Any comments from those using xl07? SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- |
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.