Formula to count data in a column that falls between a range

  • Thread starter Thread starter Paul_UK
  • Start date Start date
P

Paul_UK

i am trying to count the number of times a number between a range appears in
a column e.g. in sheet 1 i have
column1 Column2
2000 4000
3000 10000
4000 2000
5000 6000
in sheet 2 i have the criteria
A) starts at 1000 and finishes at 1999 B) starts at 2000 and finishes at 3999
C) starts at 4000 and finishes at 10000
in sheet 3 i am trying to summaries sheet 1 data on the sheet 2 criteria
Column 1 Column2
A = 0 0
B = 2 1
C = 2 3
Does anyone know of a count formula i can use to do this
 
Hi,
In sheet 3 enter for criteria A

=countif(sheet1!A1:A10000,"<=1999")

copy right to get column B

for criteria B use

=countifis(sheet1!A1:A10000,">1999",sheet1!A1:A10000,"<=3999")

Criteria C

=countif(sheet1!A1:A10000,">3999)
 
Thanks but not what i am looking for it to do, the Value say 2000 as in
criteria A may change so i am looking for the formula to change the count
result if the criteria in sheet 2 changes without having to go back into the
formula and change the <= number
 
Hi Paul,
I understan now so in sheet2 where you want the criteria just enter for
example if your criteria is as explained in A1 enter 1999, in B1 3999 then
modify the formulas changing 1999 for A1 and 3999 for B1
 
Back
Top