Counting values meeting multiple criteria.

[ExcelTip]

Problem:
Finding the number of values in List1 (Column A) between each two values in List2 (Column B).

Solution:
To count the numbers in List1 that are between 1 and 2 (B2:B3), use the following formula:
=COUNTIF($A$2:$A$10,\"\">=\"\"&B2)-COUNTIF($A$2:$A$10,\"\">\"\"&B3)

List1___List2___Result
2.4_____1_______3
3.8_____2_______2
1.3_____3_______2
4.3_____4_______2
3.5_____5_______0
2.6
1.2
1.9
4.5

[olasa]

Am I missing something or shouldn't that be...
C2: =COUNTIF($A$2:$A$10,">="&B2)-COUNTIF($A$2:$A$10,">="&B3)
-->3, 2, 2, 2, 0

Ola Sandström


Note:
=SUM(--($A$2:$A$10>=B2))-SUM(--($A$2:$A$10>=B3))
-->3, 2, 2, 2, -9 ! Wrong result!

[queue]

undefinedRegwhen I used the original : =COUNTIF($A$2:$A$10,"">=""&B2)-COUNTIF($A$2:$A$10,"">""&B3) with double quotes - it did NOT work.

I changed it to:

=COUNTIF($A$2:$A$10,">="&B2)-COUNTIF($A$2:$A$10,">="&B3)

and got desired results

[Alan]

Hi queue,

undefinedRegwhen I used the original : =COUNTIF($A$2:$A$10,"">=""&B2)-COUNTIF($A$2:$A$10,"">""&B3) with double quotes - it did NOT work.

I changed it to:

=COUNTIF($A$2:$A$10,">="&B2)-COUNTIF($A$2:$A$10,">="&B3)

and got desired results

Even better IMHO:

Enter the following array formula in C2 and copy down to C6:

{=SUM((A$2:A$10>=B2)*(A$2:A$10<=B3))}

Enter without the braces using Shift-Ctrl-Enter.

HTH,

Alan.