|
|
While working on a client's sales plan last week, I had to count the orders for a couple of specific customers. Here's a screenshot of some sample data, with the customer names in column B.
Count cells that are an exact match
In Excel, you can use the COUNTIF function to count cells that meet a specific criterion. For example, you can use this formula to count the orders that were placed by the customer, BigMart.
=COUNTIF($B$2:$B$14,"BigMart")
The first argument, $B$2:$B$14, is the range that contains the cells to be counted. The second argument, "BigMart", is the value that we want to count.
There are 3 cells that contain the exact text, "BigMart", so the COUNTIF formula returns 3.
Use a cell reference in COUNTIF
Instead of typing the text in the COUNTIF formula, you can refer to a cell that contains the text you want to count.
Continue reading Count Cells With Specific Text in Excel
If you're having a party this weekend, you can use Excel to plan the guest seating. And if you sent me an invitation, it hasn't arrived yet!
In this example, you'll enter the guest names on an Excel worksheet, then fill the tables by selecting names from data validation drop down lists. After you've selected a name, it will disappear from the drop down lists, so you can't accidentally assign one of the guests to two different seats.
Enter the Guest Names
On a worksheet named Lists, the guest names are typed in column B. Sort the names in alphabetical order, so the names will be easier to find in the data validation drop down lists.
Draw the Tables and Chairs
On a sheet named TablePlan, use the Excel drawing tools to create the tables and chairs. I used the Oval shape to draw 3 tables, with 8
Continue reading Plan Your Party Seating with Excel
This week I'm working on a client's sales plans for the upcoming fiscal year. They forecast sales per month by product and customer, and we use some pretty complicated formulas to sort things out. Of course, anywhere that it makes sense to use a pivot table, I create one. It's a great way to summarize all the details, and review the overall totals.
For example, on a worksheet you can use formulas to create a running total, but in a pivot table it's much easier -- you can quickly create running totals with a couple of mouse clicks.
Let's take a look at a pivot table based on some faked sales data. In the screen shot below, you can see the total sales per region per month, and the Grand Total per month. By changing the Sales field settings, you can show a running total, instead of the normal Sum.
Continue reading Running Totals Are Easy With Excel Pivot Tables
A very nice email, from someone who visited the Contextures website, made me think about how long I've been using Excel. My guess was that I'd started around 1987, so I fired up the old Mac laptop, and dug some old floppies out of the storage cupboard.
The laptop is a Mac PowerBook 170, from late 1991, and it's running the Mac version of Excel 3.0. There's a Mac 128K model in the back room too, but I didn't have the strength to dig that out. (I hope the producers of the tv series, The Hoarders, don't call me now.)
As you can see, the application files were much smaller in Excel 3.0. Of course, that still took a good chunk out of my 40MB hard drive.
Flashy Excel Charts
Even though the machine didn't have colour, I was still able to make some pretty flashy 3-D charts
Continue reading Back In Time With Microsoft Excel
|
|
Recent Comments