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:

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.

• Excel 2007 version with SUMIFS
• Excel 2003 version without SUMIFS

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

I use twitter more as an information source than a communication tool. That means that I am largely silent and let all my friends do the talking.
So I’m probably not fulfilling my twitter responsibilities - oh well.

Twitter has some interesting features, one of those being able to track conversations - for instance "track earthquake" will track all tweets on an earthquake. This is a great ‘personal’ meme-tracker but it would be even better if there was a public way to track memes.

It should be simple, just like how replying to tweets evolved on twitter. Twitterers started using @username to denote replies to specific people and the fine folks at twitter formalized it as funcionality so that whenever twitter sees @username the tweet goes into a ‘replies’ bucket - nice.

Now, I’m going to suggest that the fine folks at twitter institutionalize public memes. So that anyone could see the discussion on a particular topic.

Here’s what I propose, in addition to being able to do the whole ‘track’ thing for personal meme tracking. Allow ‘tagging’ of tweets via the pound/hash sign ( # ). Any tweets that are marked with #memename could then be aggregated in their own twitter page. Additionally, they could be individually trackable by track #memename.

Anyone out there who also uses twitter agree or disagree?