+ Reply to Thread
Results 1 to 6 of 6

COUNTIF over a range of cells which has a LEFT function in the condition

  1. #1
    Registered User
    Join Date
    03-02-2007
    Posts
    26

    COUNTIF over a range of cells which has a LEFT function in the condition

    I have an array of cells that have a "complex" value in each cell.
    The values have the following format:
    4 numbers, a dash, and a 3 digit alpha numeric.
    Examples would be such as : 9103-V32, 2205-133, 4455-AB6, etc ...
    Need to have the ability to find the number of times the 4 digit values occur in the range of cells.

    I have not having any luck with the following formula for an array range S12 through HM14 :
    =(COUNTIF(S12:HM14,(LEFT(S12:HM14,4) = 9103)))

    I've had success with COUNTIF with single values, but the "complex" value causes the COUNTIF to fail.
    Zero is the result returned.

    Thanks.

    J

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: COUNTIF over a range of cells which has a LEFT function in the condition

    Try

    =SUMPRODUCT(--(LEFT(S12:HM14,4)*1=9103))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: COUNTIF over a range of cells which has a LEFT function in the condition

    Hi,

    Maybe just:

    =COUNTIF(S12:HM14,"9103-???")

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    03-02-2007
    Posts
    26

    Re: COUNTIF over a range of cells which has a LEFT function in the condition

    ACE_XL : I got a bunch of ###### symbols when I used your suggestion.

    XOR_LX : I never knew I could use ???. Your reply was simple.

    Thank you both.

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: COUNTIF over a range of cells which has a LEFT function in the condition

    I think it's wise if you could:

    a) Clarify to which of the offered solutions you are referrring
    b) Increase the column width of the cell to which you are referring to reveal it's true contents

    Regards

  6. #6
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: COUNTIF over a range of cells which has a LEFT function in the condition

    ACE_XL : I got a bunch of ###### symbols when I used your suggestion.
    This would work only if all cells are populated with the first 4 characters necessarily being a number

    XOR LX's solution should address your query

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to Remove Cells = 0 and Shift Remaining Cells on Row Left w/ One Condition
    By Mark V. in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-07-2013, 12:45 PM
  2. specifying the Countif range meeting a condition
    By divi123 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-20-2013, 08:18 AM
  3. CountIF function with mulitple condition
    By gagan2sandhu in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-08-2009, 09:30 AM
  4. 2 condition countif function using AND
    By martindwilson in forum Excel General
    Replies: 2
    Last Post: 04-14-2009, 05:40 AM
  5. Countif If with 2 Criteria but with Left function in one!!
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2007, 05:22 AM

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