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!

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!

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!

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 -

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 -

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