# UDF COUNTIFS Multiple Criteria Across a Range

#### sxhall

##### Board Regular
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.

VBA Code:
``````Function COUNTTASK(Start_Time_Value, _
End_Time_Value, _
Start_Time_Range As Range, _
End_Time_Range As Range, _

Dim StaRange()
Dim EndRange()

Dim i As Long

StaRange = Start_Time_Range
EndRange = End_Time_Range

For i = 1 To UBound(StaRange)
If StaRange(i, 1) = Start_Time_Value Then
If EndRange(i, 1) = End_Time_Value Then
End If
End If
Next

End Function``````

Thanks in advance for any assistance with this.

Steven

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Fluff

##### MrExcel MVP, Moderator
You could use sumproduct to do that.
+Fluff 1.xlsm
ABCDEFGHIJK
1StartEnd
206:0014:00AAAABBBBB
306:0014:00BAAAAAAAB
406:0014:00BCCCCBBBB
508:0016:00
608:0016:00
7
8
9
1006:0014:00
11A11
12B12
13C4
Data
Cell Formulas
RangeFormula
B11:B13B11=SUMPRODUCT((\$A\$2:\$A\$6=\$A\$10)*(\$B\$2:\$B\$6<=\$B\$10)*(\$C\$2:\$K\$6=A11))

#### sxhall

##### Board Regular
You could use sumproduct to do that.
+Fluff 1.xlsm
ABCDEFGHIJK
1StartEnd
206:0014:00AAAABBBBB
306:0014:00BAAAAAAAB
406:0014:00BCCCCBBBB
508:0016:00
608:0016:00
7
8
9
1006:0014:00
11A11
12B12
13C4
Data
Cell Formulas
RangeFormula
B11:B13B11=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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
133
Replies
4
Views
65
Replies
2
Views
224
Replies
10
Views
681
Replies
7
Views
100

1,127,331
Messages
5,624,061
Members
416,008
Latest member
PETE91

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back