UDF COUNTIFS Multiple Criteria Across a Range

[sxhall]

Hi,

I have the need to do a COUNTIF on three criteria with the third being across a range of columns! Can't think of a simple way to do this using a formula so I'm attempting to write a UDF.

The below is a breakdown of what I've attempted to write in the UDF along with a sample image of the data I'm working with...

1. If the start time matches the time in the Start Column
2. and the end time is less than or equal to the time in the End Column
3. then count how many tasks match in that row
4. repeat for each row in the table/range

For example the shift 06:00-14:00 there are...

• 11 matches for 'Task A'
• 12 matches for 'Task B'
• 4 matches for 'Task C'

This is the code I have for the UDF so far. It works up to the point of where I have the COUNTTASK = COUNTIF part. If I remove this and just say COUNTTASK = 1 then the formula returns 1 for the examples where the times match.

Function COUNTTASK(Start_Time_Value, _
                    End_Time_Value, _
                    Task_Code_Value, _
                    Start_Time_Range As Range, _
                    End_Time_Range As Range, _
                    Task_Range As Range)

Dim StaRange()
Dim EndRange()
Dim TaskRange()

Dim i As Long

StaRange = Start_Time_Range
EndRange = End_Time_Range
TaskRange = Task_Range

For i = 1 To UBound(StaRange)
If StaRange(i, 1) = Start_Time_Value Then
If EndRange(i, 1) = End_Time_Value Then
COUNTTASK = WorksheetFunction.CountIf(TaskRange(i, 1), Task_Code_Value)
End If
End If
Next

End Function

Thanks in advance for any assistance with this.

Steven

 

[Fluff]

You could use sumproduct to do that.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Start	End
2	06:00	14:00	A	A	A	A	B	B	B	B	B
3	06:00	14:00	B	A	A	A	A	A	A	A	B
4	06:00	14:00	B	C	C	C	C	B	B	B	B
5	08:00	16:00
6	08:00	16:00
7
8
9
10	06:00	14:00
11	A	11
12	B	12
13	C	4
Data

Cell Formulas
Range	Formula
B11:B13	B11	=SUMPRODUCT(($A$2:$A$6=$A$10)*($B$2:$B$6<=$B$10)*($C$2:$K$6=A11))

 

[sxhall]

You could use sumproduct to do that.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Start	End
2	06:00	14:00	A	A	A	A	B	B	B	B	B
3	06:00	14:00	B	A	A	A	A	A	A	A	B
4	06:00	14:00	B	C	C	C	C	B	B	B	B
5	08:00	16:00
6	08:00	16:00
7
8
9
10	06:00	14:00
11	A	11
12	B	12
13	C	4
Data

Cell Formulas
Range	Formula
B11:B13	B11	=SUMPRODUCT(($A$2:$A$6=$A$10)*($B$2:$B$6<=$B$10)*($C$2:$K$6=A11))

Thanks Fluff,

I thought SUMPRODUCT might work but I've never got to grips with it in the past so didn't investigate it as an option. Will add this in to my list of 'need to know' for the future.

The formula worked perfectly and will be easier to change it the requirements are altered rather than updating a UDF.


Steven

 

[Fluff]

You're welcome & thanks for the feedback.