OOPS More help with formula

Y

yourproblemslvr

I need 2 formulas for the following:

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

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

Problem 2: Columns "c" thru "g" contain numbers from 1 thru 100. If th
individual number 1-100 is used (3 times, 4 times, 5 times, etc.) tota
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 numbe
is 5; the same with 2, then 3 all the way thru 100.

THANKS FOR ALL YOUR HEL
 
R

Ron Rosenfeld

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


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
 
Top