Counting cells BETWEEN two values
Posted by Bob Karrow on August 26, 2001 9:43 AM
Excel (unlike Access) does not have a "between"
operator. The formula =COUNTIF(A1:A87,"<525") will
return the number of cells with values less than
525, but what if I want to count cells whose values
are between, say 570 and 580? I've tried all the
variations I can think of using AND & IF functions,
but nothing works. Nor can I find guidance on this
in the "Help" or in various printed manuals.
I'd be most grateful if anyone could help. Thanks.
Posted by Aladin Akyurek on August 26, 2001 10:00 AM
Use:
=SUMPRODUCT((Range > Crit1)*(Range < Crit2))
where Range is the range that houses your numbers, Crit1 and Crit2 are numeric criteria with Crit2 > Crit1.
Aladin
==========
Posted by Wit on August 27, 2001 1:16 AM
How to do if I want to sum between (not count)
Thanks
Posted by Aladin Akyurek on August 27, 2001 1:50 AM
Re: How to do if I want to sum between (not count)
For multicondional sum, instead of count, it is:
=SUMPRODUCT((Range > Crit1)*(Range < Crit2),Range)
or
=SUMPRODUCT((Range > Crit1)*(Range < Crit2)*(Range))
Aladin
===========
