|
» Counting the Number of Values between Two Specified Values in a List
CATEGORY: Excel Formula and Function |
VERSIONS: All Microsoft Excel Versions |
|
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 |
  |
|
RATING:
3.03
VIEWS: 25116 |
||||
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
Sponsors:
 |
Book Store:
Recommended Books:
Related MS EXCEL TIPS:
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.
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!