22 December, 2009

Count the number of Rows after Auto-Filter in Excel

When you first apply an Auto-Filter in Excel, the Status Bar indicates the number of records in the filter (e.g. 517 of 8614). In many cases when you start performing other manipulations, the number disappears. Using a simple formula, you can calculate the filtered total.

Assume you want to count all rows that are not empty -- the CountA function -- and assume you want to count the rows in column A. The formula would be SUBTOTAL(103,A:A)-1. The syntax is as follows: subtotal(function_number, reference), where function_number is 1 of many possible options as illustrated below. I included the "-1" to remove the count for the column heading row.
function_num
(includes hidden values)
function_num
(ignores hidden values)
function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Note: I used Excel 2003 for this example.

Increase IIS on XP Maximum Connections

With increasingly more complex web pages, I found myself running into a limit in the number of IIS connections allowed for the constrained web server on Windows XP. By default Microsoft has limited the connections to 10. Even when I changed the time to release connections down to 5 minutes, I was still running into problems. With a little Google searching, I found a solution (here, here, and here).

Apparently the hard-coded limitation Microsoft has set is 40 connections. The trick is determining how to raise the level from 10 to 40. There's a script, adsutil, that can do this for you.
  1. Go to the Command Prompt and navigate to C:\Inetpub\AdminScripts.
  2. Enter "adsutil set w3svc/MaxConnections 40".
  3. If you get an error message, "This script does not work with WScript," click OK and click Yes to make Cscript as the default script for VBscript.
  4. When this completes, go back to the Command Prompt and press F3 (repeats last command) and Enter.
  5. If you were successful, the command should return "MaxConnections: 40".
  6. Stop and start IIS. You can do this as the same Command Prompt by entering "iisreset".
I suspect there may be a Registery equivalent setting, but this did the trick for me. If you don't need IIS to begin with, perhaps a better solution would be to load Apache. Microsoft Visual Web Developer may also be a solution, though I haven't spent much time with it myself.