+ Reply to Thread
Results 1 to 4 of 4

SUMIF and COUNTIF first word on cell (before first space)

  1. #1
    Registered User
    Join Date
    03-15-2014
    Location
    portugal
    MS-Off Ver
    Excel 2003
    Posts
    61

    SUMIF and COUNTIF first word on cell (before first space)

    I'm trying to use =COUNTIF(A:A;A5&" *") on a workbook to count the first word on a cell before space but it's not working.

    Example:

    DP Shirt
    DP Bike
    A123 Car

    I want to get Total=

    2x DP
    1x A123

    Workbook2.xlsx

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: SUMIF and COUNTIF first word on cell (before first space)

    Try these two

    =SUMIF(A4:A8,"DP*",B4:B8)

    =COUNTIF(A4:A8,"DP*")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,441

    Re: SUMIF and COUNTIF first word on cell (before first space)

    Try ..

    =SUMIF($A:$A,LEFT($A4,FIND(" ",$A4,1)-1)&"*",$B:$B)

    =COUNTIF($A:$A,LEFT($A4,FIND(" ",$A4,1)-1)&"*")


    Generic formula

    Copy down
    Last edited by JohnTopley; 10-17-2015 at 10:28 AM.

  4. #4
    Registered User
    Join Date
    03-15-2014
    Location
    portugal
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: SUMIF and COUNTIF first word on cell (before first space)

    Thank you Guys.

    Both worked but i had to change , with ; .

    Thank's again.

+ 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] insert space after a word in cell
    By warston in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-20-2015, 04:18 PM
  2. Is there a word macro to help me (1) combine different word files and (2) add space?
    By studyboymark in forum Word Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2015, 03:19 AM
  3. [SOLVED] formula for 'pick out the first word of the cell up until the space'
    By johnandrews in forum Excel General
    Replies: 3
    Last Post: 03-12-2014, 08:51 AM
  4. Replies: 13
    Last Post: 05-06-2009, 07:33 AM
  5. countif with any cell containing a certain word
    By Darlo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2007, 08:30 AM
  6. [SOLVED] How do I clear blank space at the top of a word-wrapped text cell?
    By mirage1210 in forum Excel General
    Replies: 2
    Last Post: 02-11-2005, 06:06 PM
  7. [SOLVED] COUNTIF text where there is more than one word in a cell
    By Abi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2005, 07:10 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