## Counting Multiple Variables in Excel

### Posted by tribeofa | Posted in MS Excel | | Posted on 14-06-2009

I don’t use the full range of Excel, and the people who create it don’t think like I do. Those two factors make the directions difficult for me to follow. Halfway through I’m thinking “What are they going on about??” It just isn’t how I would do it. If you have the same problem and have figured out a few tricks, send them in. Here’s one of my own:

Sometimes I need to count more that one variable in a line, but I can never remember the formula. I finally saved a small spreadsheet with the formula in and called it, “How to Count Multiple Variables in Excel.” Imaginative, aren’t I? If you can’t remember it either, use this formula:

=IF(AND(D3=”d”,E3 =”yes”), 1, “0″)

That means if cell D3 consists of the letter “d” and the cell E3 has the word “yes,” count it. If not, enter “0.” The last set of quotations is for the value “if false.” If you leave out the zero and just use:

=IF(AND(D3=”d”,E3 =”yes”), 1, “”)

Excel will leave the cell blank if both variables are not true.

Of course you would use your own cell numbers and entries.

Click into a blank cell at the end of the column you’ve been entering this formula into and choose “Autosum.” You can find this by clicking on ∑ in your toolbar, by choosing “function” from the insert menu (or pane in 2008), or right clicking then choosing “insert function.” If you use one of the latter methods, you’ll then need to choose “sum.” If you click on ∑ and haven’t pulled down to another choice recently, it will default to “sum.” One click totals! Any way you do it, you’ll have the total number of rows that meet your criterion of having both the specified variables. Clever bugger, isn’t it?

Have an Excel question? Send it in here. More about Excel:

Excel Wiki

Reversing Last Name First Lists into a Single Excel Column

Counting Check Marks

Everything Pasted into One Cell!

How to View Excel on Multiple Monitors

## Wikiing Up to Word

### Posted by tribeofa | Posted in MS Word | | Posted on 23-02-2009

There’s the beginnings of an MS Word pseudo-wiki here now. There are a couple of pages – one on how to reorder a last name first list (or vice versa if you’re so inclined), and a trick for using Word to count items in a list. What’s lacking are YOUR tips, so hit the contact or comment button and send in your pointers and questions. Let’s make this grow into a real tool!

The point of these software specific directories is to collect the experience of the people in our role  who really use the it, as opposed to that of tech writers who try to imagine how we’ll use it. Documentation is all well and good, but nothing helps as much as a peer.

Got a quick question? Post it on the forum.