+ Reply to Thread
Results 1 to 7 of 7

Counting values meeting multiple criteria.

  1. #1
    Kurtis
    Guest

    Count total between two dates

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

  2. #2
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Counting values meeting multiple criteria.

    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

  3. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    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!

  4. #4
    Registered User
    Join Date
    05-11-2005
    Posts
    1

    countif TIP did not work

    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

  5. #5
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: queue

    Hi queue,

    Quote Originally Posted by queue
    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
    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.

  6. #6
    Registered User
    Join Date
    11-24-2006
    Posts
    1

    Comparison between two dates.

    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.

  7. #7
    Registered User
    Join Date
    10-31-2007
    Posts
    34

    Counting numbers between 2 values in a list

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1