» Counting the Number of Values between Two Specified Values in a List
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
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)

Book Store:
Recommended Books:
- PowerPoint® 2002 For Dummies®
- Flipping Properties: Generate Instant Cash Profits in Real Estate
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Microsoft Word Version 2002 Step By Step (With CD-ROM)
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
countif TIP did not work
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
Reply: queue
Alan
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
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
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
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


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!