Podcast: Play in new window | Download
There is a wide range of music on my iPod: everything from classical to ’90s alternative. When I put the playlist on shuffle, anything goes. I might hear the soothing strains of Mozart’s “Eine kleine Nachtmusik” or the raucous refrain of Chumbawamba’s “Tubthumping.” Um…everyone else has that on their iPods, right?
The other day, the song “Counting Blue Cars” by Dishwalla came on and I found myself singing along with the lyric: “We count only blue cars…”
Nerd that I am, I couldn’t help but immediately conjure up an Excel formula to help them out:
=COUNTIF(Cars, “blue”)
It might be completely missing the point of the song (about which I’m clueless), but being able to count and sum based on conditions in Excel can be really useful.
The formula I used for counting blue cars is a good example of a simple COUNTIF doing what it does best: counting only those items that match the criteria you specify. In that formula, “Cars” could be replaced by a range of cells in an Excel worksheet that contain the names of different colors. The formula will return a number that represents how many of the cells in that range match the criteria.
COUNTIF’s summing counterpart is known as SUMIF. It works in much the same way: you tell it which cells need to match a criteria, you specify the criteria they must match, then you specify which cells will be summed as a result. This last group of cells can be in another column if needed. For example, a SUMIF formula can examine the text in cells C2 through C16 to determine which ones contain the text “red,” then sum the values in the corresponding cells from column B. So if C5 and C8 were “red,” the values in B5 and B8 would be summed.
In these examples, we’re just writing the criteria inside double quotes so that an exact match must be found. But let’s say you wanted to count how many cells match an approximate condition. For instance, if you specify the criteria to be “*ito” the formula will return a count of how many cells end in the letters “ito.” So burrito, taquito, and mosquito would all match the criteria. Mmm…I’m starting to get hungry now…and slightly itchy.
The asterisk in that formula is called a wildcard—it represents any number of characters. Likewise, you can use a question mark to represent a single character.
When you need to count cells that don’t have similarities, such as enchiladas and wool sweaters, using wildcards won’t work; but you can combine multiple COUNTIF formulas to get the results you need.
If any of this sounds confusing, just check out this week’s tips and you’ll get a better idea of how to set up these formulas.
Links to the tips are on the blog entry for this week’s podcast at www.productiveUpodcast.com, and they include:
- Let’s Sum If It Fits: Using the SUMIF function
- Count By Conditions: Using the COUNTIF function
- Creative Conditions: Using wildcard characters as criteria for SUMIF and COUNTIF
- Summing This Not That: Using the SUMPRODUCT function to sum or count using multiple conditions
- Arrayed in Splendor: Creating an array formula to sum based on multiple conditions
We’d love to hear your feedback on the tips and the podcast, so head on over to iTunes to write a review for us! And don’t forget that you can follow us on Twitter @productiveUcast.
Thanks for joining us for this week’s podcast. With these tips, you may soon find yourself counting blue, red, and green cars all with a single formula. See you next week!
Music courtesy of Clayton & Fulcrum
