# Proper syntax for SUMIF with wildcard

1. ## 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. ## Re: Problems with SUMIF

Try

=SUMIF(\$G\$3:\$G\$120,"*"&A9&"*",\$H\$3:\$H\$120)

3. ## Re: Problems with SUMIF

YES! that worked brilliantly, thank you Jeff

4. ## Re: Proper syntax for SUMIF with wildcard

You are most welcome and thanks for the feedback.

##### Users Browsing this Thread

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

#### 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