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:
Reversing Last Name First Lists into a Single Excel Column
Everything Pasted into One Cell!
How to View Excel on Multiple Monitors
Share on Facebook
