given one date i would like to count the total number of dates which would be found within the month
given one date i would like to count the total number of dates which would be found within the month
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
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!
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
Hi queue,
Even better IMHO:Originally Posted by queue
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks