Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
countifs
Hi guys,
I have a spreadsheet that has a list of locations in column B and in column C I have a list of values, such as Telephone, Email and On site. I need to count the number of Telephones, Email etc for each location. Can anyone please help me. Thanks Submitted via EggHeadCafe - Software Developer Portal of Choice ASP.NET AJAX Automatically Saving Web Form Data http://www.eggheadcafe.com/tutorials...omaticall.aspx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
countifs
You could use a pivot table and manual put it in with the worksheet menu. Here is equivalent code. Modify the 1st three line as required to change the sheet names and the start row. Sub CountItems() 'change 3 lines as required Set Sourcesht = Sheets("Sheet1") Set Destsht = Sheets("Sheet2") StartRow = 1 NewCol = 2 NewRow = 2 RowCount = StartRow With Sourcesht Do While .Range("B" & RowCount) < "" Location = .Range("B" & RowCount) Item = .Range("C" & RowCount) With Destsht 'find location in column A Set c = .Columns("A").Find(what:=Location, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then InsertRow = NewRow .Range("A" & InsertRow) = Location NewRow = NewRow + 1 Else InsertRow = c.Row End If 'Find Items in Row 1 Set c = .Rows("1").Find(what:=Item, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then InsertCol = NewCol .Cells(1, InsertCol) = Item NewCol = NewCol + 1 Else InsertCol = c.Column End If 'add data to table .Cells(InsertRow, InsertCol) = _ .Cells(InsertRow, InsertCol) + 1 End With RowCount = RowCount + 1 Loop End With End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164964 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countifs | Excel Worksheet Functions | |||
countifs | Excel Worksheet Functions | |||
CountIfs | Excel Programming | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
Countifs Fx in 07 how in 03? | Excel Discussion (Misc queries) |