Use array to qualify to columns

Posted by jsavery on December 20, 2001 8:28 AM

I have to columns, one containg a district, the second containg a date.

I would like to get a count of those districts that have a date enterd in the column.

I have tried to get the AND function to do this, qualifying the district being equal and the date being > 0, but I cannot get this to work.

Please advise.

A B
Dist Date

001 12/19
001
002 12/19
002 12/01

Result: 001 = 1, 002 = 2

Thanks,
James Savery

Posted by Aladin Akyurek on December 20, 2001 8:36 AM

Make a unique list of districts (using e.g., Advanced Filter) in D from D1 on (with D1 housing a label e.g., district).

In E2 enter: =SUMPRODUCT(($A$2:$A$25=D2)*(LEN($B$2:$B$25)>0))

where A2:A25 houses the districts and B2:B25 the dates if any. Copy this down for all districts.

Aladin

========

Posted by Mark W. on December 20, 2001 8:37 AM

James, this is easily accomplished using a PivotTable.
Just put 'Dist' in the ROW or COLUMN area, and
put 'Date' in the DATA area with the COUNT summary
function.