OOPS More help with formula

[yourproblemslvr]

I need 2 formulas for the following:

Problem 1: Columns "c" thru "g" contain numbers from 1 thru 100. If the numbers is those columns are 50 or less then I NEED TO ADD ONE, the sum to go in one column; then the sum of numbers between 51 to 100 to go into another column.

FOR EXAMPLE: IF COLUMN C THRU "G" HAS 7 NUMBERS THAT ARE LESS THAN 50 I NEED TO SHOW 7. IF THEY SHOW 3 NUMBERS BETWEEN 51 AND 100 THEN I NEED TO SHOW 3.

Problem 2: Columns "c" thru "g" contain numbers from 1 thru 100. If the individual number 1-100 is used (3 times, 4 times, 5 times, etc.) total the I need to show the number of times it was used in the columns.

For example: If 1 is used 5 times in columns C thru G then the number is 5; the same with 2, then 3 all the way thru 100.

THANKS FOR ALL YOUR HELP

[Ron Rosenfeld]

On Sat, 13 Aug 2005 09:07:54 -0500, yourproblemslvr
<[email protected]> wrote:

>
>I need 2 formulas for the following:
>
>Problem 1: Columns "c" thru "g" contain numbers from 1 thru 100. If the
>numbers is those columns are 50 or less then I NEED TO ADD ONE, the sum
>to go in one column; then the sum of numbers between 51 to 100 to go
>into another column.
>
>FOR EXAMPLE: IF COLUMN C THRU "G" HAS 7 NUMBERS THAT ARE LESS THAN 50 I
>NEED TO SHOW 7. IF THEY SHOW 3 NUMBERS BETWEEN 51 AND 100 THEN I NEED
>TO SHOW 3.
>
>Problem 2: Columns "c" thru "g" contain numbers from 1 thru 100. If the
>individual number 1-100 is used (3 times, 4 times, 5 times, etc.) total
>the I need to show the number of times it was used in the columns.
>
>For example: If 1 is used 5 times in columns C thru G then the number
>is 5; the same with 2, then 3 all the way thru 100.
>
>THANKS FOR ALL YOUR HELP


This assumes the problem is exactly as you state, and that all cells in the
range are either blank, or have numbers in the range of 1 to 100.

You can use the COUNTIF worksheet function for both problems:

50 or less:
=COUNTIF(C:G,"<=50")
Greater than 50:
=COUNTIF(C:G,">50")

For any given number (e.g. one)
=COUNTIF(C:G,1)

For this last one, you could, for example:

A1: 1
A2: 2
A3: 3
..
..
..

B1: =COUNTIF(C:G,A1)
B2: =COUNTIF(C:G,A2)
B3: =COUNTIF(C:G,A3)
..
..
..



--ron