Excel 2010: Summing it Up, Filter or Table

In the last two lessons, we learned about Filtering and Excel’s Format as Table command.  Open up the dog breed spreadsheet we’ve been working on.  If you need the data, copy and paste the spreadsheet at the bottom into Excel.

 

Let’s say we want to count our short hair dogs.  The keyword here is “Count.”  We are not totaling something, say adding the inches, instead we are counting the number of dogs.  In Excel go to the cell where you want the answer, then type “=”.  This creates a formula.  The formula we want is =counta which counts cells that have something in them; it counts anything that is not an empty cell.

In the column labeled Short, go to the empty cell just below the last dog, here it would be Cell B15.  Type “=counta” and add an open parenthesis “(“:

=counta(

The open parenthesis tells Excel that you are going to give it a range of cells on which to work its magic.  Now, drag through the data between the heading “Short” and where your formula is (Cell B2 through Cell B14).  Type close parenthesis “)” to tell Excel you are finished with the range, then press Enter.  There are seven short hair dogs in our list.  Excel also added a total line to our table.  Do the same procedure in the Long, Wired, and Curly columns.  Copy and Paste do not work quite the same in tables, so just create the =acount() formula in each column.

This gives us a count of coat types, but it doesn’t tell us things like how many dogs from England have short hair.  Let’s filter out all the dogs except the dogs that originated in England by clicking the filter triangle in the Country of Origin column, click on the check next to Select All to deselect all the countries, then just click the checkbox next to England and OK.  Only two dogs are now visible, but did our numbers change?  No.  When we are working with filters, Excel thinks of all numbers as subtotals; to us it may be subtotal, average, and count, but to Excel they are all subtotals.  Go back to the filter triangle and select all the data so we are back where we started.

Go back to the cell where we counted short hair dogs, and enter the subtotal function right over the counta function by typing:

                =subtotal(

As soon as you type the open parenthesis, Excel gives you lots of options for your subtotal function. 

We want to count, but only the filtered results, right?  So double-click on “counta” which will enter the code 3 in your formula.  The 3 has no mathematic value, it is simple a command for Excel to use the counta function when the results are filtered.  Finish your formula by typing a comma after 3, dragging through the cell range (Cell B2 through Cell B14), and typing the closing parenthesis “)”.  Copy and paste the rest of this row.  Now let’s filter our results and count the German dogs.

Play with your table for a while until you are comfortable.

________________________________________________________

Sample Spreadsheet to Copy & Paste into Excel:

Dog Breed	Short	Long	Wired	Curly	Max Size Inches	Country of Origin
Belgian Shepherd	x	x		x	26	Belgium
Boxer	x				25	Germany
Chihuahua	x	x			9	Mexico
Dachshund	x	x	x		15	Germany
Flat Coated Retriever		x			24	England
Fox Terrier	x		x		15	England
German Shepherd	x	x			26	Germany
Irish Wolfhound		x	x		32	Ireland
Labrador Retriever	x				24	Canada
Maltese		x			10	Greece
Portugeuse Water Dog		x		x	23	Portugal
Shetland Sheepdog		x			16	Scotland
Standard Poodle		x		x	21	Germany