November
27
2007
Wow! It’s been a really, really long time since I have done one of these. According to WordPress, the last "Sharing the Wealth" post was published on September 27, 2006 – discussing how to store color assigments in VBA. Guess I haven’t been doing a lot of Excel work in the last year!
All that aside, today’s tip is just too obvious not to pass a long, although you may want to file it under ReadTheManual…
Someone asked me today "How can I match rows where the string contains the substring "abc" and total those rows?" Ok, so I’m paraphrasing but this was the question. Being a little rusty I went right along with it and said something like "Right, you can’t use SUMIF because it doesn’t do fuzzy matching."
WRONG! WRONG! WRONG!
After I got off the phone, I hopped into Excel help, not because I thought it would tell me something about SUMIF that I didn’t know but because there might be another formula to help me out or at least a kernel of an idea.
It turns out that SUMIF supports the wildcards ? and * (question mark and asterisk). Furthermore you can match on those characters be prefixing them with a ~ (tilde).
Here are some sample conditions, the appropriate wild-carding and the SUMIF formulae:
| CONDITION | MATCH | EXCEL FORMULA |
| SUM IF ends with "string" | "*string" | =SUMIF(A2:A11,"*string",B2:B11) |
| SUM IF starts with "string" | "string*" | =SUMIF(A2:A11,"string*",B2:B11) |
| SUM IF contains "string" | "*string*" | =SUMIF(A2:A11,"*string*",B2:B11) |
| SUM IF "string" is preceded by any one character | "?string" | =SUMIF(A2:A11,"?string",B2:B11) |
| SUM IF "string" is followed by any one character | "string?" | =SUMIF(A2:A11,"string?",B2:B11) |
| SUM IF contains "stringA" OR "stringB" | "*stringA*", "*stringB*" | =SUM(SUMIF(A2:A11,"*stringA*",B2:B11),SUMIF(A2:A11,"*stringB*",B2:B11)) |
| SUM IF contains "stringC" AND "stringD" | "*stringC*", "*stringD*" | =SUMIFS(B2:B11,A2:A11,"*stringC*",A2:A11,"*stringD*") |
Ok, So the last two are a bit of a trick. We’re using SUM as a parent function to the two SUMIF functions to create the OR operator and the new in Excel 2007 SUMIFS function to match on multiple criteria for the AND operator. SUMIFS is just like SUMIF but matches on multiple criteria (can be from multiple locatioins too) but notice that the SUM RANGE comes first instead of the CRITERIA RANGE which comes first in the more widely known SUMIF.
Not enough? You can download this sample spreadsheet to see it in action.
This is another in a long litany of my "DUH" moments with Excel, what about you? What’s your biggest DUH moment with Excel?
Related Posts:
- Sharing the Wealth: Random Excel Tip
- Sharing the Wealth: Random Excel Tip #2
- Sharing the Wealth: Random Excel Tip #3
Tags: data analysis, excel, Excel 2003, Excel 2007, formula, formulae, function, functions, SUMIF, SUMIFS
Here’s a little tip I used recently after having a ‘duh’ moment:
What I had : Smith,John (no space)
What I needed: Smith, John (with space)
Assuming the name is in cell A2 here is the formula to convert it:
=substitute(A2,”,”,”, “)
Notice the space before the last quotation mark.
Tony,
Good One! A very simple solution where people might over-think (e.g. write some VBA with a SPLIT, etc).