+ Reply to Thread
Results 1 to 4 of 4

Proper syntax for SUMIF with wildcard

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    Proper syntax for SUMIF with wildcard

    I'm using the following formula:

    =sumif($G$3:$G$120,"*ancient*",$H$3:$H$120) to add certain cells in column H (if the cells in column G of the same row contain the word 'ancient'), and it's working fine.

    But I'd like to refer to a cell instead of have to type in the word 'ancient', because I have a whole column of different words to apply this formula to. The problem is, if I use =sumif($G$3:$G$120,A9,$H$3:$H$120) or =sumif($G$3:$G$120,"A9",$H$3:$H$120) or =sumif($G$3:$G$120,"*A9*",$H$3:$H$120), the formula doesn't work (I just get 0, instead of the actual sum), because the list in column G is a bunch of phrases that may or may not contain the word ancient (it's a CONTAINS, not an EQUAL TO thing). How can I write this so that I can copy and paste the formula without having to type in new words in the place of 'ancient' every time?

    Thank you!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,095

    Re: Problems with SUMIF

    Try

    =SUMIF($G$3:$G$120,"*"&A9&"*",$H$3:$H$120)
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    12-27-2012
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Problems with SUMIF

    YES! that worked brilliantly, thank you Jeff

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,095

    Re: Proper syntax for SUMIF with wildcard

    You are most welcome and thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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