Count number of times each injury occurs in the current month

[lwolfe]

Long title I know but it is what I can't Figure out.

I have the Dates in column B
and the injuries are in column K

I have been tring to use a seperate list in column V to match up to K and count each time it finds the injury then go to the next type. My problem is that when it goes to look for them it searches the entire column not just for the current month.

I have attached the workbook in a zip file for ease of understanding my problem. I have tried to comment out as best as possible what is happening throughout the Macro5 this is my problem child.

Any help would be Great.

Thanks in advance.

[acw]

Hi

You can do this pretty easily using the SUMPRODUCT function.

On sheet Air Quality, put the formula
=SUMPRODUCT(--(MONTH('Air Quality'!$B$7:$B$89)=MONTH('Air Quality'!$C$1)),--($G$7:$G$89=V8))
in X8 and copy down to X13.

In X18 put in the formul
=SUMPRODUCT(--(MONTH('Air Quality'!$B$7:$B$89)=MONTH('Air Quality'!$C$1)),--($K$7:$K$89=V18))
and copy down. You will need to expand the entries in column V to include a unique list of all the options available to be entered into column K.


HTH

Tony

[ALe]

Pivot table?

[lwolfe]

Sorry for the delay, long holiday weekends always get me behind.
ALe The Pivot Table dosen't really give me what I am after so I will try the code form ACW over the weekend and let you know what happens.

Thanks,
LWolfe

[matthewspatrick]

LWolfe,

I did a little fancy footwork with a PivotTable, and got an answer. Working from the Air Quality worksheet...

1) Unmerge the cells in your header row (Row 6)

2) Add the following UDF:


' Function based on post by Brad Yundt
' http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21221177.html
Option Explicit
Public Function RetrieveSplitItem(Text As String, Separator As String, Item As Variant, _
Optional CaseSen As Boolean = False)
' Returns a specified substring from a larger string (Text) separated by a specified
' character sequence (Separator)
Dim X As Variant
If CaseSen Then
X = Split(Text, Separator, -1, vbBinaryCompare)
Else
X = Split(Text, Separator, -1, vbTextCompare)
End If

If IsNumeric(Item) And (Item < 1 Or Item > (UBound(X) + 1)) Then
RetrieveSplitItem = CVErr(xlErrNA)
ElseIf Not IsNumeric(Item) And Item <> "L" And Item <> "l" Then
RetrieveSplitItem = CVErr(xlErrNA)
Else
If Item = "L" Or Item = "l" Then Item = UBound(X) + 1
RetrieveSplitItem = X(Item - 1)
End If

End Function



3) In S6, put a new label. I put in Injury. In S7, put:
=RetrieveSplitItem(K7," ",1)
and copy that formula down as needed

4) Create your PivotTable, using Date of Injury and Injury as ROW fields and (count of) Injury as the DATA field.

5) After finishing the PivotTable, rightclick the gray field label for Date of Injury, select Group and Outline|Group from the popup menu, and group it by years and months (hold doen the Ctrl key to select both on the dialog box)

[OBP]

Why do so many posters struggle to program Excel "databases" to do what Access does naturally with queries?

[mdmackillop]

Why do so many posters struggle to program Excel "databases" to do what Access does naturally with queries?
They love the challenge!:rotlaugh: