| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function
Try these:
=COUNTIF(A1:A100,1) =COUNTIF(A1:A100,2) Adjust the range to suit. Biff wrote in message ps.com... 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function
Thank you so very much! Worked like a charm!
On Jun 30, 1:00 pm, "T. Valko" wrote: Try these: =COUNTIF(A1:A100,1) =COUNTIF(A1:A100,2) Adjust the range to suit. Biff wrote in message ps.com... 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!- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function
You're welcome. Thanks for the feedback!
Biff wrote in message ups.com... Thank you so very much! Worked like a charm! On Jun 30, 1:00 pm, "T. Valko" wrote: Try these: =COUNTIF(A1:A100,1) =COUNTIF(A1:A100,2) Adjust the range to suit. Biff wrote in message ps.com... 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!- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function
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 " wrote: 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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 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. Put this formula first under your columns... =COUNTIF(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+C OLUMN())&(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+C OLUMN())&(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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002: Auto Sum function not working in large Excel file | Excel Discussion (Misc queries) | |||
| challenge! javascript function into excel function | Excel Worksheet Functions | |||
| FUNCTION GETPIVOTDATA MICROSOFT EXCEL 2003 VS EXCEL 2004 FOR MAC | Excel Worksheet Functions | |||
| Excel Workday Function with another function | Excel Discussion (Misc queries) | |||
| Can you nest a MID function within a IF function in Excel | Excel Worksheet Functions | |||
Hybrid Mode
