+ Reply to Thread
Results 1 to 2 of 2

COUNTIF with AND function

  1. 10-25-2012, 03:31 PM #1
    warn6087
    warn6087 is offline
    Registered User
    Join Date
    10-25-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    1

    COUNTIF with AND function

    I appologize if this issue has been addressed before. I found a lot of topics on COUNTIF problems, but none that answered my question.

    I need to count the number of cells in a dataset (A1:A100) that contain a number that is greater than the value in B1, but I want to know how those numbers are distributed. For instance, how many of those cells contain a value that is between 1 and 2 greater than B1. I feel like this formula should work, but it isn't.

    =COUNTIF(A1:A100,(AND(">="&(B1+1),"<="&(B1+2))))

    This formula is just spitting out zeroes. Let me know what I am doing wrong.
    Last edited by warn6087; 10-25-2012 at 03:52 PM.
    Reply With QuoteRegister To Reply
  2. 10-25-2012, 03:42 PM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,791

    Re: COUNTIF with AND function

    The trick is to have two countifs and subtract one from the other:

    =COUNTIF(A1:A100,">="&B1+1) - COUNTIF(A1:A100,">"&B1+2)

    The first countif counts ALL the numbers more than B1+1, which obviously includes the numbers which are more than B1+2, so the second countif removes those from the count.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1