Can someone turn this equation into a custom function?

[Darranimo]

I am trying to learn custom functions and could use help with this formula:

=IF(E$2="ACTUAL",COUNTIFS(tblClsd[udAbbr],$B12,tblClsd[ClsDate],"<="&E$5,tblClsd[ClsDate],">"&D$5),"")

I am embarrassed to show my attempt but here it is anyway:

Function CountClosedDeals(udAbbr As String, clsDateStart As Date, clsDateEnd As Date, actual As String) As Integer
Dim tbl As ListObject
Dim abbr As Range
Dim clsd As Range
Set tbl = Worksheets("tblClsd").ListObjects("tblClsd")
Set abbr = tbl.ListColumns("udAbbr").DataBodyRange
Set clsd = tbl.ListColumns("ClsDate").DataBodyRange

If actual = "ACTUAL" Then

CountClosedDeals = Application.WorksheetFunction.CountIfs(abbr, udAbbr, clsd, "<=" & clsDateEnd, clsd, ">" & clsDateStart)

Else
CountClosedDeals = ""
End If
End Function

If you can show me where I went wrong I would really appreciate it!

 

[awoohaw]

This is not VBA, but a LAMBDA that you create the function in the name manager and then call the function.
In the worksheet snippet, I test the Lambda by "seeding" the parameters after the function.
The CALC in F8 is standard Excel Formula.
The CALC in F10 is my Lambda making/testing
The CALC in F12 is actually using the custom LAMBDA function.

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2					ACTUAL
3									tblClsd
4				Date 1	Date 2				ClsDate	udAbbr
5				1/20/2023	1/29/2029				1/16/2023	DOMDZ
6									1/17/2023	RTYPE
7									1/17/2023	KIIMQ
8						1			1/18/2023	SYMHU
9									1/19/2023	PCEME
10					MAKING LAMBDA:	1			1/19/2023	ZVZEX
11									1/20/2023	VHRIB
12	an Abbr	ZVZEX			Count_Abbr_in_Date_Range:	1			1/21/2023	ZVZEX
13									1/22/2023	HESGN
14									1/23/2023	HVYQU
15
Sheet18

Cell Formulas
Range	Formula
F8	F8	=IF(E$2="ACTUAL",COUNTIFS(tblClsd[udAbbr],$B12,tblClsd[ClsDate],"<="&E$5,tblClsd[ClsDate],">"&D$5),"")
F10	F10	= LAMBDA(Status,Date1,Date2,Abbr,colDates,colAbbrs, IF(Status="ACTUAL",COUNTIFS(colAbbrs,Abbr,colDates,">="&Date1,colDates,"<="& Date2),""))(E2,D5,E5,B12,I5:I14,J5:J14)
F12	F12	=Count_Abbr_in_Date_Range(E2,D5,E5,B12,I5:I14,J5:J14)

 

[Alex Blakenburg]

Assuming you are practicing making a UDF then try changing your If statement to the below:
- the Ucase is a just in case. It would be better to have a variable name that is more obviously a variable
- the main change is adding CLng to convert the dates. Dates can be a bit tricky

Oh by the way, you are more likely to get a response by following @awoohaw's lead and posting an XL2BB sample of you data. It often takes as long to set up some test data as coming up with a solution.

If UCase(actual) = "ACTUAL" Then
    CountClosedDeals = Application.WorksheetFunction.CountIfs(abbr, udAbbr, clsd, "<=" & CLng(clsDateEnd), clsd, ">" & CLng(clsDateStart))
Else
    CountClosedDeals = ""
End If

 

[Darranimo]

This is not VBA, but a LAMBDA that you create the function in the name manager and then call the function.
In the worksheet snippet, I test the Lambda by "seeding" the parameters after the function.
The CALC in F8 is standard Excel Formula.
The CALC in F10 is my Lambda making/testing
The CALC in F12 is actually using the custom LAMBDA function.

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2					ACTUAL
3									tblClsd
4				Date 1	Date 2				ClsDate	udAbbr
5				1/20/2023	1/29/2029				1/16/2023	DOMDZ
6									1/17/2023	RTYPE
7									1/17/2023	KIIMQ
8						1			1/18/2023	SYMHU
9									1/19/2023	PCEME
10					MAKING LAMBDA:	1			1/19/2023	ZVZEX
11									1/20/2023	VHRIB
12	an Abbr	ZVZEX			Count_Abbr_in_Date_Range:	1			1/21/2023	ZVZEX
13									1/22/2023	HESGN
14									1/23/2023	HVYQU
15
Sheet18

Cell Formulas
Range	Formula
F8	F8	=IF(E$2="ACTUAL",COUNTIFS(tblClsd[udAbbr],$B12,tblClsd[ClsDate],"<="&E$5,tblClsd[ClsDate],">"&D$5),"")
F10	F10	= LAMBDA(Status,Date1,Date2,Abbr,colDates,colAbbrs, IF(Status="ACTUAL",COUNTIFS(colAbbrs,Abbr,colDates,">="&Date1,colDates,"<="& Date2),""))(E2,D5,E5,B12,I5:I14,J5:J14)
F12	F12	=Count_Abbr_in_Date_Range(E2,D5,E5,B12,I5:I14,J5:J14)

Give me your brain, please! lol Thank you!!

 

[awoohaw]

happy to help.
Although I respect VBA, I try to use worksheet formula solutions first. Particularly if the people using the workbooks are unfamiliar with VBA. That does have a downside though, as some folks may tinker and break worksheet formulas!