Excel Function

F

fuller.susieq

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!
 
R

Ron Coderre

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
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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
 
Top