+ Reply to Thread
Results 1 to 5 of 5

Finding a tag or text within text as part of SUMIFS formula

  1. #1
    Forum Contributor
    Join Date
    11-02-2003
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    111

    Finding a tag or text within text as part of SUMIFS formula

    I've been looking at SUMIFS for the first time. In the MS help, they refer to using wildcard characters ? and *

    One thing I would like to be able to do is use a formula like this to sum the contents of column O:O whereever there is mention of Mick within column Y:Y.

    Column Y:Y contains notes up to 100 characters against each line item to do with delivery times, contact details etc.

    =SUMIFS(Sheet1!O:O,Sheet1!Y:Y,"=*mick")

    Thanks

    tw
    Terry

    "... I thought I was a power user of Excel until I came onto these boards..."

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,644

    Re: Finding a tag or text within text as part of SUMIFS formula

    You might like to put an asterisk at the end of the text as well:

    =SUMIFS(Sheet1!O:O,Sheet1!Y:Y,"*mick*")

    Also, the = is not necessary as it is implied if omitted.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    11-02-2003
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    111

    Re: Finding a tag or text within text as part of SUMIFS formula

    Lovely thanks Pete

    Not essential to my life but is there a formula that can refer to a separate cell where the name Mick was entered and then find the text within the text ?

    Thanks again..

    Tw

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,644

    Re: Finding a tag or text within text as part of SUMIFS formula

    Yes, put Mick in cell Z1 (say), then you can have this:

    =SUMIFS(Sheet1!O:O,Sheet1!Y:Y,"*"&Z1&"*")

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    11-02-2003
    Location
    Birmingham, UK
    MS-Off Ver
    Office 365
    Posts
    111

    Re: Finding a tag or text within text as part of SUMIFS formula

    Brilliant thanks again Pete

+ 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. Formula to identify part of a text in cell from range of cells & insert adjacent cell text
    By Novicebutnotforlong in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-19-2013, 02:11 AM
  2. [SOLVED] Sumifs Finding two text in one column
    By zicktechnology in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2013, 04:17 AM
  3. Macro: select a part of a text and compare it with a part of another text
    By vladozah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2012, 06:37 AM
  4. Finding part of a text string in a cell
    By Tonro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2008, 05:59 PM
  5. [SOLVED] Finding specific text in string - Part II
    By Hardip in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-08-2006, 09:25 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