counting conditional numbers

  • Thread starter Thread starter turk
  • Start date Start date
T

turk

I have two columns in column b I have numbers that only 1-
4. in column c ihave numbers from 0-100. What i would
like to do is compare column b with c. specifically I
would like to count grades. match the number 4 and count
in column c how many are greater than 90, and count when
coulumn c is between 80 and 90, greater than 70 and less
than 80, count for greater than 60 and less than 70.

Heres what I would like to see:
90 80 70 60
1
2
3 TABLE SHOWS A COUNT OF GRADE SCORES THAT MATCH
4 1,2,3,4

I can do this in programming laguage with conditions and
counters but I don't know how to do it excel?
 
Tur

Assuming that the data is in the range B3 to C12 (for this exercise) and the output is going in the range F3 to I6

In E3 to E6 enter 1,2,3 and
In F2 to I2 enter 90, 80, 70 and 6

In F3 enter the formul
=SUMPRODUCT(--($B$3:$B$12=$E3),--($C$3:$C$12>F$2)
and copy down to F6

In G3 enter the formul
=SUMPRODUCT(--($B$3:$B$12=$E3),--($C$3:$C$12>=G$2))-SUM($F3:F3
and copy across and down to I6

This will fill the matrix with the counts of numbers in the ranges

You will have to modify the data ranges as required. Depending on how variable they are, it may pay to make the data area named ranges, then use the name in the formula

Tony
 
~× said:
*Turk

Assuming that the data is in the range B3 to C12 (for this exercise
and the output is going in the range F3 to I6.

In E3 to E6 enter 1,2,3 and 4
In F2 to I2 enter 90, 80, 70 and 60

In F3 enter the formula
=SUMPRODUCT(--($B$3:$B$12=$E3),--($C$3:$C$12>F$2))
and copy down to F6.

In G3 enter the formula
=SUMPRODUCT(--($B$3:$B$12=$E3),--($C$3:$C$12>=G$2))-SUM($F3:F3)
and copy across and down to I6.

This will fill the matrix with the counts of numbers in the ranges.

You will have to modify the data ranges as required. Depending o
how variable they are, it may pay to make the data area named ranges
then use the name in the formula.

Tony
Click to expand...
 
Back
Top