If statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column with various numbers. I want to be able to count certain
numbers, such as 0-15,16-30, and so on.

I want to be able to write an if statetment that says if a number is between
0 and 15, count it as a 1, 16-30 counted as a 2.

Then need to perform a specific count for just 1's, 2s,....

Can you help me
 
Assuming you don't have any negative numbers you could use a countif()
=countif(A1:A100,"<=15") would return the count of numbers between 0 - 15
for numbers between 16-30 you could use
=countif(A1:A100,"<=30")-countif(A1:A100,"<=15")
 
Could you create another column of data for 1's and 2s from the listed data?
If so, put this in it:

=MAX(INT((A2-1)/15)+1,1)

Where A2 is the cell location of the numbers you want to count.
 
This works well for the first sitution, 0-15, but if i use the other; <=30,
this also counts the first condition. How do I only count for the specified
conditons without overlapping?
 
You would have to subtract out the first condition. If you put the
<=15 formula in say cell G2. For the <=30 formula just put
=countif(A1:A100,"<=30")-G2 into cell G3. It will count all the
numbers <=30 and then subtract the amount that are <=15 so
in a sense you will just end up with numbers beween 16-30. If you then
want numbers between 31-45 in G4 you could put
=countif(A1:A100,"<=45")-sum(G2:G3)
 
Hi Metalteck

i'm guessing this is still the same workbook i looked at the other night, if
so, adjust the formulas as follows:
AO3 : don't change
AP3 : use
=IF($Q3="","",IF(AND($U3<=(AP$1/1440),$U3>(AO$1/1440)),2,0))
AQ3 : use
=IF($Q3="","",IF(AND($U3<=(AQ$1/1440),$U3>(AP$1/1440)),3,0))
AR3 : use
=IF($Q3="","",IF(AND($U3<=(AR$1/1440),$U3>(AQ$1/1440)),4,0))

this will put a 2 under the 30, a 3 under the 45 and a 4 under the 60
then you can use
to count the number of 1's
=COUNTIF($AO$3:$AR$2000,1)
to count the number of 2's
=COUNTIF($AO$3:$AR$2000,2)
etc
 
Back
Top