+ Reply to Thread
Results 1 to 7 of 7

countifs?

  1. 03-26-2019, 12:33 PM #1
    leydet4
    leydet4 is offline
    Registered User
    Join Date
    04-02-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    countifs?

    I have a spreadsheet that i am trying to count how many occurrences of the word "medic" occurs in a row. The rows stay constant. The word medic appears multiple times in a single cell is this possible I have attached a row that shows what I am looking for.

    I am looking for the sheet to count how many medics are listed in "F" "N" "X" and "AA" so if it has 3 medics listed I want it to populate in "AP" with the amount

    I have tried everything I can think of with my basic knowledge of count ifs

    any help would be greatly appreciated
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-26-2019, 12:48 PM #2
    dosydos
    dosydos is offline
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2308
    Posts
    1,472

    Re: countifs?

    Put this in AP2 and drag down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 03-26-2019, 12:51 PM #3
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: countifs?

    Please try at AP2
    =(LEN(F2&N2&X2&AA2)-LEN(SUBSTITUTE(LOWER(F2&N2&X2&AA2),"medic",)))/5
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  4. 03-26-2019, 01:02 PM #4
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: countifs?

    Here is a "magic formula" =(LEN(F4&N4&X4&AA4)-LEN(SUBSTITUTE(UPPER(F4&N4&X4&AA4),"MEDIC","")))/5

    LEN(F4&N4&X4&AA4) concatenates the strings and takes the length of the string.

    LEN(SUBSTITUTE(UPPER(F4&N4&X4&AA4),"MEDIC","") does the same thing except that all occurrences of "MEDIC" are taken out.

    So let's say that the sting contains two instances of the word Medic and is overall 50 characters in length.

    The first part of the formula yields 50. The second part of the formula takes out two medics to the tune of 5 characters each, so it yields 40.

    50 - 40 = 10 and since the word Medic is 5 characters, divide the difference by 5 to get the number of times medic appears.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
    Reply With QuoteRegister To Reply
  5. 03-26-2019, 01:04 PM #5
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: countifs?

    Well, it seems everyone had the same idea at the same time
    Reply With QuoteRegister To Reply
  6. 03-26-2019, 01:06 PM #6
    dosydos
    dosydos is offline
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2308
    Posts
    1,472

    Re: countifs?

    I like you and Bo's better, Concat is a lot cleaner
    Reply With QuoteRegister To Reply
  7. 03-26-2019, 01:13 PM #7
    leydet4
    leydet4 is offline
    Registered User
    Join Date
    04-02-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: countifs?

    Thank you everyone helps a lot!!
    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)

Similar Threads

  1. Multiplying COUNTIFS using a VLOOKUP table rather than making lots of COUNTIFS
    By DIAL90 in forum Excel General
    Replies: 6
    Last Post: 03-19-2019, 09:14 PM
  2. [SOLVED] Sumproduct to replace countifs as countifs don't work on external source reference
    By KrishnaSagar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2017, 02:33 AM
  3. How can I run CountIfs + CountIfs without double counting?
    By hiitsjessie in forum Excel General
    Replies: 1
    Last Post: 02-06-2017, 04:49 PM
  4. [SOLVED] Countifs + Countifs - replacement?
    By JulieQ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2015, 03:02 PM
  5. COUNTIFS OR COUNTIFS needs to work
    By joshbellfi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-23-2013, 10:55 AM
  6. COUNTIFS OR...multiple countifs without duplication in the numbers
    By HooligaD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2012, 09:53 AM
  7. Excel 2007 : More efficient: 2 long COUNTIFS or 1 COUNTIFS and 1 VLOOKUP based on the COUNTIFS?
    By dip11 in forum Excel General
    Replies: 0
    Last Post: 12-16-2011, 09:01 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1