Google Exceltip.com
Account Icon
Shopping Cart
CheckOut

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

Problem:

Finding the number of values in List1 (Column A) that are between each two successive values in List2 (Column B).

Solution:

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

Screenshot // Counting the Number of Values between Two Specified Values in a List

Counting the Number of Values between Two Specified Values in a List
Rate this tip
12 34 5
  RATING: 3.03
  VIEWS: 25116
olasa wrote on May 07, 2005 05:06 EST
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!
countif TIP did not work
queue wrote on May 11, 2005 10:00 EST
[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
Reply: queue
Alan wrote on May 12, 2005 01:49 EST
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.
Count total between two dates
Kurtis wrote on December 31, 1969 19:00 EST
given one date i would like to count the total number of dates which would be found within the month
Comparison between two dates.
shDeepak wrote on November 24, 2006 01:43 EST
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.
Counting numbers between 2 values in a list
digita wrote on October 31, 2007 18:52 EST
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



REGISTERED USERS click here to post comments


GUESTSclick here to Register
Name
Comment Title
Comments


Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Copyright © 2003 ExcelTip.com
Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation