+ Reply to Thread
Results 1 to 4 of 4

Using cell numbers to replace numerals in COUNTIFS criteria

  1. #1
    Registered User
    Join Date
    01-19-2013
    Location
    House
    MS-Off Ver
    Excel 2011
    Posts
    8

    Using cell numbers to replace numerals in COUNTIFS criteria

    I'm creating a COUNTIFS formula that tabulates matched occurrences for basic criteria in a range of cells (let's call the range of cells A2:A200) but want to use other cells as references for the basic criteria.

    So my formula might look like this: =COUNTIFS(A$2:A$200,">1",A$2:A$200,"<2") meaning that it should tabulate anything greater than 1 and less than 2.

    When I try to use a cell reference for 1 and 2 (let's call it B1 for 1 and B2 for 2) and then use those cells in the formula instead of the actual numerals, I get an error.

    Following the way that the formula above is laid out, I've tried the formula this way: =COUNTIFS(A$2:A$200,">B1",A$2:A$200,"<B2")

    Is there a proper syntax to allow the use of cells instead of values in the criteria portion of a COUNTIFS formula?

    Thanks!

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Using cell numbers to replace numerals in COUNTIFS criteria

    try it like this

    ">"&B1
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    01-19-2013
    Location
    House
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Using cell numbers to replace numerals in COUNTIFS criteria

    Thanks! The ampersand was exactly what I was missing (I'm coming from a database world that uses the & differently so I didn't even know & was an option)

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Using cell numbers to replace numerals in COUNTIFS criteria

    Your very much welcome.Yup. Commands are sometimes twisted in use on diff. appl.
    And thanks also for the star tap.


    Btw, can you mark this thread solved. Thanks.


    Regards,
    Vladimir

+ 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