Multiconditional countif
Posted by Uttam on June 18, 2001 1:22 PM
Hello,
I have the following values in the cells indicated
A2 = 10
A3 = 180
A4 onwards down the column are values which would be something like
9,
120
131
156
181
and so on till, say, A45
I would like to enter a formula in A1 which will give a count of entries in the range A4:A45 which are greater than 10 (also in cell A2) and less than 180 (also in A3)
Can anyone suggest a formula for entering in A1?
Thank you in advance.
Uttam
Posted by Mark W. on June 18, 2001 1:37 PM
{=SUM((A4:A45>A2)*(A4:A45<A3))}
This is an array formula which must be entered
using the Shift+Control+Enter key combination.
The braces, {}, are not entered by you. They're
supplied by Excel in recognition that the formula
has been appropriately entered as an array formula.
Posted by Mark W. on June 18, 2001 1:38 PM
{=SUM((A4:A45 > A2)*(A4:A45 < A3))}
This is an array formula which must be entered
using the Shift+Control+Enter key combination.
The braces, {}, are not entered by you. They're
supplied by Excel in recognition that the formula
has been appropriately entered as an array formula.
Posted by Aladin Akyurek on June 18, 2001 1:40 PM
Multiconditional Count
Hi Uttam
Array-enter
=SUM((A4:A45>A2)*(A4:A45 < A3))
In order to array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time (not just enter).
If you'd like to avoid using the above array-formula, then in B4 enter:
=((A4>$A$2)+0)*((A4 < $A$3)+0) [ copy down as far as needed ]
In B3 just enter: =SUM(B4:B45) to get the count.
Aladin
Posted by Uttam on June 18, 2001 1:55 PM
Hello Aladin and Mark,
Both your answers were helpful indeed.. Thank you very much.
