Archive of Mr Excel Message Board

If you add column headers (labels) to your data
(i.e, {"Date","Fruit"}) you can then do the following:

1. After selecting a single cell in the worksheet area
containing your data (and the new headers), choose
the Data PivotTable Report... menu commmand.
2. At Step 1 of 4 press the "Next>" button.
3. At Step 2 of 4 press the "Next>" button.
4. At Step 3 of 4 drag the "Date" field name to
either the ROW or COLUMN area, and the "Fruit"
field name to the DATA area.
5. At Step 4 of 4 decide where you want to place
the resulting PivotTable and press "Finish"
6. Now right-click on the newly created PivotTables
"Date" button and choose the Group and Outline Group...
command. The dialog will popup pre-configured for a
Month grouping so just press the OK button.

There you have it!

Sorry I did not explain myself enough - I have tried to use the Privot Table and Group Date functions and they do not produce the result I want.

Going back to my apples and pears exercise.
I should have said that I have another breakup of data - there are 2 types of apples - I need to be able to count how many red apples and how many green apples I have for say the month of February.- and I want the user to have the option of selecting the date eg. InputBox or Dropdown list with the months listed and the whole process to run via the click of a command button on a form over the top of the excel sheet. This data I intend to link to another sheet to produce a printed form with the end result.

this is take 2 so I hope I have explained myself this time.

Many thanks to Mark W for your suggestion.

Thanks once again. I think this Mr Excel's is a great way for all of us to resolve problems. I only just found it the other day.

Hi Christine

Here is some code to help you!

Option Explicit

Dim i As Integer
Dim MyDate As Date
Dim Date1 As String
Dim Date2 As String

Private Sub CommandButton1_Click()

If ComboBox1.ListIndex > -1 And _
ComboBox2.ListIndex > -1 Then
CommandButton1.TakeFocusOnClick = False
Application.ScreenUpdating = False
Date1 = Format("1/" & ComboBox1 & "/2001", "dd/mm/yyyy")
Date2 = Format("1/" & ComboBox2 & "/2001", "dd/mm/yyyy")

With Sheet5
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter

.Cells(1, 1).AutoFilter Field:=1, Criteria1:=">=" & Date1, _
Operator:=xlAnd, Criteria2:="<=" & Date2

.Columns(2).SpecialCells(xlCellTypeVisible).Copy _
Destination:=.Range("IV1")

TextBox1 = WorksheetFunction.CountIf(.Columns(256), "Apples")

.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End If
End Sub

Private Sub UserForm_Activate()

For i = 1 To 12
MyDate = DateValue("1/" & i & "/2000")
ComboBox1.AddItem (Format(MyDate, "mmmm"))
Next i
For i = 1 To 12
MyDate = DateValue("1/" & i & "/2000")
ComboBox2.AddItem (Format(MyDate, "mmmm"))
Next i
End Sub

If you like I could send you the example workbook.

Dave

OzGrid Business Applications

Assuming that I understood your problem correctly, I'd favor the use of PivotTables as Mark suggested. You will probably be convinced of that when you see a formula-based solution as the one that follows.

Step 1. Select the cells (say, D2:D10) in which you have the dates and name the selection DATES via the Name Box (or via the option Insert,Define,Name).
Step 2. Select the cells (say, E2:E10) in which you have the items (red apples, etc) and name the selection ITEMS.
Step 3. Enter the unique items (item names) in, say, G2:G6.
Step 4. Enter the month numbers (d.i., 1 to 12) in, say, H1:S1.
Step 5. Array-enter (that is, hit control+shift+enter at the same time to enter) the following formula in H2:

=SUM((MONTH(DATES)=H$1)*(ITEMS=$G2))

Copy this formula to I2:S3, then select H2:S2 and do an AutoFill down to H6:S6.

After Step 5 you get a table of counts per item X month.

Although I'm not sure,you apparently want users to enter an item and a month and give them a count as output.

If this is correct, you can do the following.

Step A. Enter the months January..December in, say, U1:U12 and the month numbers 1..12 in V1:V12.
Step B. Select U1:U12 and name the selection MonthsList.
Step C. Select U1:V12 and name the selection MONTHS.
Step D. Select G2:G6 and name the selection ItemsList.
Step E. Select G2:S6 and name the selection DATA.
Step F. Select A3, Name the selection Item, activate while in A3 the option Data,Validation, at Settings tab, choose List for Allow and enter =ItemsList for Source. Enter 'Please select an item' on Input Settings as Input message.
Step G. Select A4, Name the selection Month, activate while in A4 the option Data,Validation, at Settings tab, choose List for Allow and enter =MonthsList for Source. Enter 'Please select a month' on Input Settings as Input message.
Step H. Type in, say, A8:

=VLOOKUP(Item,DATA,VLOOKUP(Month,MONTHS,2,0)+1,0)

You can get a copy of the workbook that includes the above machinery.

However, you should really try PivotTables as Mark suggested.

Aladin

I have got to agree! Pivot Tables are pretty hard to beat.

You should be able to group your dates by Month and Year etc and place them in the Page Field. You get a list of dates to filter your table by.

But if you haven't used them very much, they can
be challenging(But worth it).

Dave

OzGrid Business Applications

Regards
Christine

Many thanks to you too for the code - I will also try this and see how I go - I have been working with the Pivot Tables and they just don't do quite what I want. Maybe I am asking too much and need to look at arranging my data again - the apples and pears is just simple case serenro

Also can you sent me a copy of the workbook as I do have trouble understanding VB.

Thanks again
Christine

It was me not Aladin that wrote the code for you :-)

I would be happy to send you the example Workbook though! If you like I could also set up a Pivot Table for you perusal, if you are interested just E-Mail me.

Dave

OzGrid Business Applications

Sorry Dave, Aladin did write me some code as well - I did reply to both of you and I do appreciate your help. Email is on the way.