count value for multi condition from one column in excel 2003

[imran khan]

lets suppose we have a series of values in a column ( 15, 20, 14, 18, 21....)

so i want to count the value which are greater than 15 and less than 20 from the series in excel 2003.


waiting for reply

Imran Khan

 

[oldchippy]

Hi,


Try this


=COUNTIF(A1:A30,"<20")-COUNTIF(A1:A30,">15") adjust range as required

 

[imran khan]

hi,

i tried this but answer is not correct......

 

[ahhhmed]

Hi,

Try this


=COUNTIFS(A1:A30,">="&15,A1:A30,"<="&20)

 

[ahhhmed]

Or this


=COUNTIFS(A1:A30,">"&15,A1:A30,"<"&20)

 

[imran khan]

hi ahhmed,

above 2 formulas works in excel 2007 and 10 .. but in excel 2003 it does not work..

 

[Hui]

=SUMPRODUCT((A1:A30>=10)*(A1:A30<=20))


Adjust Ranges and >= and <= as appropriate

 

[imran khan]

hi Hue,

thats great.... now its work as per my requirement ....

thanks Hui,,

wish you best of luck