+ Reply to Thread
Results 1 to 11 of 11

Using SUMIF with a keyword as part of the criteria

  1. #1
    Registered User
    Join Date
    01-15-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    19

    Using SUMIF with a keyword as part of the criteria

    Happy Days to all - I am looking to get help with using a keyword search as part of a criteria for a SUMIF statement.

    I have attached my working copy for you to see.

    I would like G2 (I am only using September at this point) to populate the sum of C2:C8, only if D2:D8 contains the word Education. Similar for Government and below with their respective words.

    I am not sure if the word was always at the beginning would help, but that is how it would be input in the worksheet.

    Thank you for any help!
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Using SUMIF with a keyword as part of the criteria

    Try

    =SUMIF($D$2:$D$8,"*"&F2&"*",$C$2:$C$8)

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Using SUMIF with a keyword as part of the criteria

    In G2

    =SUMIF($D$2:$D$8,"*"&$F2&"*",$C$2:$C$8)

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using SUMIF with a keyword as part of the criteria

    Maybe this...

    =SUMIF(D$2:D$8,F2&"*",C$2:C$8)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    01-15-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Using SUMIF with a keyword as part of the criteria

    Quote Originally Posted by Jonmo1 View Post
    Try

    =SUMIF($D$2:$D$8,"*"&F2&"*",$C$2:$C$8)
    That worked perfectly - can you explain briefly what I am looking at with the criteria portion?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Using SUMIF with a keyword as part of the criteria

    Glad to help

    The *'s are wildcards. Indicating to look for the text in F2 while any characters can appear before or after it.

    The & is shorthand for Concatenate (to join 2 text strings into 1)

    So if F2 is "Education"
    Then
    "*"&F2&"*" = "*Education*"

    The formula would read it like
    =SUMIF($D$2:$D$8,"*Education*",$C$2:$C$8)

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using SUMIF with a keyword as part of the criteria

    Quote Originally Posted by cdeguzman View Post

    I am not sure if the word was always at the beginning would help, but that is how it would be input in the worksheet.
    If the keyword is always at the beginning of the string then you can use the shorter version in post #4.

  8. #8
    Registered User
    Join Date
    01-15-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Using SUMIF with a keyword as part of the criteria

    Hey Thanks everyone for a fast and accurate response!

  9. #9
    Registered User
    Join Date
    01-15-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Using SUMIF with a keyword as part of the criteria

    Quote Originally Posted by Jonmo1 View Post
    Glad to help

    The *'s are wildcards. Indicating to look for the text in F2 while any characters can appear before or after it.

    The & is shorthand for Concatenate (to join 2 text strings into 1)

    So if F2 is "Education"
    Then
    "*"&F2&"*" = "*Education*"

    The formula would read it like
    =SUMIF($D$2:$D$8,"*Education*",$C$2:$C$8)
    Perfect - I can use this going forward!

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Using SUMIF with a keyword as part of the criteria

    Quote Originally Posted by Tony Valko View Post
    If the keyword is always at the beginning of the string then you can use the shorter version in post #4.
    Ahh, I only saw the first part of this sentence.
    Quote Originally Posted by cdeguzman View Post
    I am not sure if the word was always at the beginning would help, but that is how it would be input in the worksheet.
    Thought it meant that the string doesn't necessarily always begin with the keyword..
    I didn't see that as an instruction that it was always at the beginning..

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using SUMIF with a keyword as part of the criteria

    You're welcome. We appreciate the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Replies: 6
    Last Post: 03-18-2014, 11:16 AM
  2. [SOLVED] Sumif Keyword
    By rick434 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-23-2013, 12:53 AM
  3. Filter List by Keyword & copy the result in column by keyword as header
    By kitunga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2013, 07:16 AM
  4. Replies: 2
    Last Post: 07-13-2012, 04:02 AM
  5. [SOLVED] Insert text in Cell A1 based on keyword criteria
    By [email protected] in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-13-2006, 12:10 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