+ Reply to Thread
Results 1 to 4 of 4

Trying to use a wildcard in a countif

  1. #1
    Registered User
    Join Date
    04-08-2017
    Location
    Dixon, CA
    MS-Off Ver
    2007
    Posts
    5

    Trying to use a wildcard in a countif

    Please forgive as I am a novice over my head. Not even sure how to word the post.

    Below is the current array formula I have and it works in previous worksheets. The new worksheet differs in thas the letters after the "2-" changing all the time, so I only want to countif "2-*.
    =IF(COUNTIF(TEST_TIME7, "2-WAC")>=ROWS($1:$1),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$1))),"") The second and third formula are the same but the ROWS($1:$1) change to ROWS($1:$2) and then 3.

    TEST_TIME7 is one of the columns of assignments for the 7 o'clock period.
    TEST_NAMES is the column with the workers names to return from.

    This worksheet is a little different in that after the "2-" the remaining letters now vary. (Before they were the same every time. I just want to still count it if it has the "2-with anything else". I have tried the * (ie. "2-*) and get a #NUM! error.
    I use the ROW to look for the second and third time it occurs in the same column.

    Full Worksheet has about 40 rows and 11 columns.
    TEST
    NAMES TEST_TIME
    7AM
    NAME 1 2-WAC =IF(COUNTIF(TEST_TIME7,"2-WAC")>=ROWS($1:$1),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$1))),"")
    NAME 2 2-WAC =IF(COUNTIF(TEST_TIME7,"2-WAC")>=ROWS($1:$2),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$2))),"")
    NAME 3 2-WAC =IF(COUNTIF(TEST_TIME7,"2-WAC")>=ROWS($1:$3),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$3))),"")

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Trying to use a wildcard in a countif

    delete by kev - posted in error
    Last edited by kev_; 02-22-2018 at 02:20 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Trying to use a wildcard in a countif

    Hi,

    You cant use a wildcard in the second part, so you would need a LEFT function
    =IF(COUNTIF(TEST_TIME7, "2-*")>=ROWS($1:$1),INDEX(TEST_NAMES,SMALL(IF(LEFT(TEST_TIME7,2)="2-",ROW(TEST_NAMES)-4),ROWS($1:$1))),"")
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    04-08-2017
    Location
    Dixon, CA
    MS-Off Ver
    2007
    Posts
    5

    Re: Trying to use a wildcard in a countif

    Thank you Kev, Does the LEFT function go before or after the COUNTIF? I will read up on it.

    Thanks again. Bill

+ 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. [SOLVED] COUNTIF with wildcard
    By makinmomb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2017, 02:42 AM
  2. CountIf Wildcard Help!
    By SilverBullet17 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-16-2017, 03:33 AM
  3. COUNTIF with Wildcard characters
    By rfadams in forum Excel General
    Replies: 1
    Last Post: 10-20-2011, 02:44 PM
  4. Countif and Using WildCard
    By computerlady143 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-30-2011, 11:12 AM
  5. Wildcard COUNTIF problem.
    By shawnstorm in forum Excel General
    Replies: 6
    Last Post: 01-21-2010, 04:56 AM
  6. countif with numerical wildcard
    By Dandi in forum Excel General
    Replies: 3
    Last Post: 09-02-2009, 04:15 PM
  7. countif wildcard
    By mystic342005 in forum Excel General
    Replies: 1
    Last Post: 07-29-2007, 02:48 PM

Tags for this Thread

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