» Counting the Number of Values between Two Specified Values in a List

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!

[FONT=Arial]undefined[/FONT][COLOR=Black]Reg[/COLOR]when 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

Hi queue,

[QUOTE=queue][FONT=Arial]undefined[/FONT][COLOR=Black]Reg[/COLOR]when 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[/QUOTE]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.

given one date i would like to count the total number of dates which would be found within the month

suppose i hav 2 dates column i.e From and To. And a particular value is assigned to each range. i want that if i give any date1 it should be compare that with 2 dates column form 1st cell to last cell and display the particular value that is assigned to that range in which date1 lies.

Using the range from the tip, the working formula should be:

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

Alternatively, using the following sumproduct function yields the same correct result:

=SUMPRODUCT((A2:A10>B2)*(A2:A10<B3))

Hope this helps