+ Reply to Thread
Results 1 to 5 of 5

Formula to count the number of "Marry" with last name "Sims" regardless of M.I.

  1. 06-01-2017, 07:24 PM #1
    spsssw
    spsssw is offline
    Registered User
    Join Date
    09-19-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    3

    Angry Formula to count the number of "Marry" with last name "Sims" regardless of M.I.

    FN MI LM
    Marry A Sims
    Marry B Lorh
    Marry C Sims
    Marry A Sims
    James B Leposki
    James C Pohsa
    James A Lamin
    James B Sik
    James C Jakes
    Bobby A Poliski
    Bobby B Molak
    Bobby C Jensen
    Joeyzi A Jorehn
    Joeyzi B Bills
    Joeyzi C Kerov

    First row is only labels. There 15 names.
    Need a formula that count the number of "Marry" with last name "Sims" regardless of middle name initial. Cannot create any new cells / rows. There is to be ONE formula and work in ONE cell that counts the number of Marry with last name "Sims". The formula need to encompass all 15 names and all cells.

    I have a list of thousands of names and I need to count the number of with the same FN and LN, ignoring MI, (for each name!) and I am only allowed ONE cell to get the count.

    I tried this

    =COUNTIF(C2:C16,VLOOKUP("Marry",A2:C16,1,TRUE),"Sims"))

    Exce said too many argument. It's frustrating I cannot come up with a workable formula. Thank toy for the help.
    Reply With QuoteRegister To Reply
  2. 06-01-2017, 07:32 PM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Need a formula that count the number of "Marry" with last name "Sims" regardless of M.

    A
    B
    C
    D
    2
    		 Marry A Sims
    3
    		 C2: =COUNTIFS(A2:A16, "Marry *", A2:A16, "* Sims")
    3
    		 Marry B Lorh
    4
    		 Marry C Sims
    5
    		 Marry A Sims
    6
    		 James B Leposki
    7
    		 James C Pohsa
    8
    		 James A Lamin
    9
    		 James B Sik
    10
    		 James C Jakes
    11
    		 Bobby A Poliski
    12
    		 Bobby B Molak
    13
    		 Bobby C Jensen
    14
    		 Joeyzi A Jorehn
    15
    		 Joeyzi B Bills
    16
    		 Joeyzi C Kerov
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 06-01-2017, 08:31 PM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to count the number of "Marry" with last name "Sims" regardless of M.I.

    Or

    =COUNTIF(A2:A16, "Marry * Sims")
    Reply With QuoteRegister To Reply
  4. 06-01-2017, 11:10 PM #4
    wladoD
    wladoD is offline
    Registered User
    Join Date
    08-16-2011
    Location
    Thailand
    MS-Off Ver
    Excel 2019
    Posts
    66

    Lightbulb Re: Formula to count the number of "Marry" with last name "Sims" regardless of M.I.

    Hi,

    Have a look at this.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-02-2017, 02:17 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Formula to count the number of "Marry" with last name "Sims" regardless of M.I.

    wladoD Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    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. formula to count the number occasion
    By sab17 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2019, 07:37 AM
  2. Formula to count number of visible rows, and formula to count visible blanks
    By radoncadonc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-28-2015, 04:19 PM
  3. [SOLVED] Formula To Count Number Of Instances
    By mamuka12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2013, 12:45 AM
  4. [SOLVED] Formula to count the number of spaces before text/number is written in a cell.
    By kmis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-12-2013, 05:05 PM
  5. How to marry a Ballance Sheet to a P& L
    By Gil Pahlow in forum Excel General
    Replies: 0
    Last Post: 11-22-2012, 01:37 AM
  6. Count Formula-count the number of times
    By admiral7921 in forum Excel General
    Replies: 3
    Last Post: 01-07-2011, 09:07 PM
  7. count cells down with a number in formula
    By leighmills33 in forum Excel General
    Replies: 4
    Last Post: 04-30-2009, 08:11 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