 |
 | 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 / June 2007Excel Function
I am working in a worksheet in which 1 is equal to a Want and 2 is = to a Desire. I need to tally each column so that at the bottom of the sheet one row will add up all the 1's in each column while ignoring the 2's in that column. On the very next row, I need to tally each column so that it will count how many 2's are in each column while ignoring the 1's. I know there is a way I can write an IF statement to do this, I just can't figure it out. Please help! Need asap. Thanks! Try these: =COUNTIF(A1:A100,1) =COUNTIF(A1:A100,2) Adjust the range to suit. Biff >I am working in a worksheet in which 1 is equal to a Want and 2 is = > to a Desire. [quoted text clipped - 10 lines] > > Thanks! Thank you so very much! Worked like a charm! > Try these: > [quoted text clipped - 25 lines] > > - Show quoted text - You're welcome. Thanks for the feedback! Biff > Thank you so very much! Worked like a charm! > [quoted text clipped - 27 lines] >> >> - Show quoted text - Try the COUNTIF function: Example: =COUNTIF($A$1:$A$10,2) ...Counts the number of 2's in A1:A10 =COUNTIF($A$1:$A$10,1) ...Counts the number of 1's in A1:A10 Does that help? *********** Regards, Ron XL2002, WinXP > I am working in a worksheet in which 1 is equal to a Want and 2 is = > to a Desire. [quoted text clipped - 10 lines] > > Thanks! >I am working in a worksheet in which 1 is equal to a Want and 2 is = > to a Desire. [quoted text clipped - 8 lines] > I know there is a way I can write an IF statement to do this, I just > can't figure it out. Please help! Need asap. Put this formula first under your columns... =COUNTIF(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&(ROW()-1)),"=1") It counts the number of 1s in the column. Put this formula under it... =COUNTIF(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&(ROW()-2)),"=2") It counts the number of 2s in the column. Now select both of the cells you just put the formulas in and copy across for all of your columns. These formulas will automatically account for your adding or deleting rows in the future. Rick |
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.