MS Excel Tips and Tricks – How to count things in Excel

Posted on February 8, 2008 at 6:43 am

There’s an old wives’ tale that says most of us only use about ten percent of our brains. Well, that’s poppycock. But what is true is that most of us use far less than 10 percent of the power in our expensive Microsoft Office applications. Take Excel for example. I love Excel. If you could take the most horrible job in the world and figure out a way to use Excel to do it, I think I would find it fun.

People often ask me how I learned Excel. I started by skimming a big, fat How to Use Excel manual, just to get an idea of what kinds of things I could do with it. If I could conceive of a use I could make of a feature, I learned how to make use of it. After that it was just learning by doing. But enough about me.

One of the most powerful features available in Excel is its use of formulas. In each cell of an Excel spreadsheet you can enter data, like numbers or text. You can also enter formulas, and formulas can do almost anything with the data in other cells.

In this post I will show you how to use a simple formula to count things in Excel. Let’s pose a problem: You have a list of friends who each have a certain amount of money they owe you from different times they borrowed. That’s right, you’re like a bank. So your list looks like this:

Now, if this is the full extent of your Shylock ways, you could just add up how much each friend owes you. But if you have a lot of this stuff going on and you want to automate the process of adding what each person owes, you could set up your spreadsheet to do it for you, like this:

So what formulas produce the sums of the amounts owed by each friend? Simple: SUMIF. Using the SUMIF formula, I set up a little table that automatically calculates the sum totals owed by each friend.

Here’s what I did: I made a list of my friends in Column E. Then in cell F2 I entered the following:

=SUMIF($B$2:$B$9,”Mike”,$C$2:$C$9)

In the next cell down,

=SUMIF($B$2:$B$9,”Janet”,$C$2:$C$9),

and so on, only changing the name for Excel to use as its criterion. What you are telling Excel to do is, look in the range B2 to B9 (the $ signs make it so you can copy the formula without the range being changed); if it sees the word Mike, it should include the adjacent amount in the range C2 to C9 it its sum. Simple.

If you think about it you can probably think of some ways you can use the SUMIF formula. But you should only use this power for good, not evil.

If you enjoyed this post, make sure you subscribe to my RSS feed!

» Filed Under MS Office Tips

2 Responses to “MS Excel Tips and Tricks – How to count things in Excel”

MJ said on : February 8th, 2008 at 12:31 pm

Thanks for the helpful tip!

Dovan said on : April 10th, 2008 at 8:10 pm

hi there,

can someone help me?

i have a list of a customer name, each customer has many site
when i filter them, i don’t know how many the customer service site?