ansaurus

Question

Answer 1

+1 A:

If that's all the table has, this should work:

SELECT datecol, count(*) FROM table GROUP BY datecol

Eric Petroelje 2009-10-09 20:45:18

Answer 2

A:

You can use Pivot Table to do this.

Use Items as Row Field.

Use Date as Column and Data Field (Count of Date).

Using VBA

Assuming that Items and Date are in worksheet "Sheet1" starting in cell A1, the pivot table is created in a worksheet "Sheet2" starting in cell A3 in the active workbook.

Sub createPivotTableReport()

  Set wsData = ActiveWorkbook.Worksheets("Sheet1")
  Set wsReport = ActiveWorkbook.Worksheets("Sheet2")

  Set rngData = wsData.UsedRange
  Set rngReport = wsReport.Range("a3")

  Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, rngData.Address(True, True, 1, True))
  Set pvtTable = pvtCache.CreatePivotTable(rngReport)

  pvtFieldsRow = Array("Items")
  pvtFieldsCol = Array("Date")

  pvtTable.AddFields pvtFieldsRow, pvtFieldsCol
  pvtTable.PivotFields("Date").Orientation = xlDataField

End Sub

Note:

In some (newer?) versions of Excel, the line

Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, rngData.Address(True, True, 1, True))

can be changed to

Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, rngData)

Or maybe my old version of Excel is just bad. I don't know.

I also posted a .vbs solution here:

http://gallery.technet.microsoft.com/ScriptCenter/en-us/dde1e5e2-f5f6-4053-84fb-b820f01f1fdf

imfrancisd 2009-10-11 04:13:11

Answer 3

A:

How about using Excel's DCOUNTA function? Have two tables, where the top table is the criteria for the DSUM function.

EX)

Date         Items
01.09.2009   IT004

Date         Items
01.09.2009   IT004
01.09.2009   IT004
01.09.2009   IT005

So on a line below the bottom table, you'd put the DCOUNTA function:

=DCOUNTA(A3:B6,"items",A1:B2)

Always look for built-in functionality first :)

JakeTheSnake 2009-10-20 01:54:34

ansaurus

tags:

views:

answers:

Counting rows by the different date

related questions