UDF COUNTIFS Multiple Criteria Across a Range

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
114
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'

1614268648293.png


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, _
                    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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
114
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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top