CountIf and And

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

Guest

Hi all. I'm hoping someone can help me with this formula. I have a list of ages ranging from 1 to 100. I only want to count those between 20 and 30. I can get the first part of the formula to work - meaning I can count the number greater than 20. But when I try to add the 'And', I run into problems. Below is what I have so far. Can anyone help with the other half? Thanks
Lind
=COUNTIF(Data!I$3:$I$1500,">19")
 
Hi Linda
one way:
=COUNTIF(Data!I$3:$I$1500,">=20") - COUNTIF(Data!I$3:$I$1500,"<=30")

or use
=SUMPRODUCT((Data!I$3:$I$1500>=20)*(Data!I$3:$I$1500<=30))
 
Would this do it for you?

=COUNTIF(Data!I$3:$I$1500,">19")-COUNTIF(Data!I$3:$I$1500,">30")
(everthing over 19 - everthing over 30 = everthing between 20 & 30
(assumming you are using whole numbers only)


Linda said:
Hi all. I'm hoping someone can help me with this formula. I have a list
Click to expand...
of ages ranging from 1 to 100. I only want to count those between 20 and
30. I can get the first part of the formula to work - meaning I can count
the number greater than 20. But when I try to add the 'And', I run into
problems. Below is what I have so far. Can anyone help with the other
half? Thanks!
 
Hi
the first formula should read
=COUNTIF(Data!I$3:$I$1500,">=20") - COUNTIF(Data!I$3:$I$1500,">30")
 
Back
Top