Function with countifs and search text in cells

Question

I hope somebody can help, I have been stuck on this for days.

In my excel sheet I have a list of errors (not related to excel) which I want to count in a function. The problem is that some type of errors contains a unique reference which makes the countifs function in excel insufficient.

There are 4 column ranges I want to search in and 4 criteria’s (ConigneeIdRange, ConsigneeId, ErrorMessageRange, ErrorMessage, DateRange1, Date1, DateRange2, Date2). For most errors the countifs function is sufficient, because there is no unique reference in the cell (string), but I have 2 type of error messages that contains a unique value (shipment number). The first one contains the text “Unable” and the second contains the word “modified” in the string.

I have a list of 2000 rows and would like to know how many times a type of error occurred in a cell in each row within a specific month.

Perhaps a loop could be the solution? I don’t know how to add more criteria’s to below code and how to search within a string for the 2 type of errors.

Option Explicit
Function CErrorCountLoop(ByVal ConsigneeIdRange As Variant, ByVal ConsigneeId As Variant)

    Dim vItem As Variant
    Dim iCount As Integer

    iCount = 0

    For Each vItem In ConsigneeIdRange

        If vItem = ConsigneeId Then
            iCount = iCount + 1
        End If
    Next vItem

    CErrorCountLoop = iCount

End Function

See below the sample data:

1. Date Error Message Consignee ID Result Desired
2. 43101 Invalid input 7777778 2 3
3. 43103 Invalid input 7777778 2 3
4. 43151 Invalid input 7777778 2 0
5. 43131 Unable to process 445376 7777775 2 2
6. 43104 Modified by another process 445378 7777774 2 2
7. 43115 Unable to process 445380 7777775 2 2
8. 43119 Modified by another process 445390 7777774 2 2
9. 43120 Invalid input 7777779 1 1
10. 43101 Invalid input 7777778 2 3

Formula used: =COUNTIFS($C$2:$C$9,C2,$B$2:$B$9, IF(NOT(ISERROR(FIND("Modified",B2))),"Modified",IF(NOT(ISERROR(FIND("Unable",B2))),"Unable",B2)),$A$2:$A$9,">="&DATE(2018,1,1),$A$2:$A$9,"<="&DATE(2018,1,31))

In above formula the wildcards are removed after saving the post, but they are in the formula I used.

As you can see I didn't get the desired result. Can somebody help me to create a function which produce the desired results?

Answer 1

I found a solution for my issue.

Option Explicit Function CErrorCount(ConsigneeIdRange As Range, ConsigneeId As Long, ErrorMessageRange As Range, ErrorMessage As String, _ Date1Range As Range, Date1 As String, Date2Range As Range, Date2 As String) As Integer

'Counts the type of errors between dates

If InStr(1, ErrorMessage, "Unable", 1) Then
    CErrorCount = WorksheetFunction.CountIfs(ConsigneeIdRange, ConsigneeId, ErrorMessageRange, "*Unable*", Date1Range, Date1, Date2Range, Date2)
ElseIf InStr(1, ErrorMessage, "modified", 1) Then
    CErrorCount = WorksheetFunction.CountIfs(ConsigneeIdRange, ConsigneeId, ErrorMessageRange, "*modified*", Date1Range, Date1, Date2Range, Date2)
Else
    CErrorCount = WorksheetFunction.CountIfs(ConsigneeIdRange, ConsigneeId, ErrorMessageRange, ErrorMessage, Date1Range, Date1, Date2Range, Date2)

    End If

End Function

Answer 2

If I understand your problem, you can use wildcards within your COUNTIFS, first checking whether H2 contains the text "Modified" or "Unable". If so, the fourth argument of COUNTIFS will use wildcards, otherwise it will just be H2.

=COUNTIFS(K2:K2000, K2, H2:H2000, IF(NOT(ISERROR(FIND("Modified",H2))),"*Modified*",IF(NOT(ISERROR(FIND("Unable",H2))),"*Unable*",H2)), B2:B2000, ">="&DATE(2018,1,1), B2:B2000,"<="&DATE(2018,1,31))

---

EDIT 1

This screenshot is my understanding of the data/formula you provided, with some revisions and comments as to why your results do not match the desired result. This is the revised formula I am using.

=COUNTIFS($C$2:$C$10,C2,$B$2:$B$10, IF(NOT(ISERROR(FIND("Modified",B2))),"Modified*",IF(NOT(ISERROR(FIND("Unable",B2))),"Unable*",B2)),$A$2:$A$10,">="&DATE(2018,1,1),$A$2:$A$10,"<="&DATE(2018,1,31))

1. Just to check, it looks like your data runs from C2:C10, but your formula only references C2:C9(?)
2. The COUNTIFS with wildcards is returning the correct results on the lines with "Unable" or "Modified"
3. On line 4, how is the desired result 0? The result is 3 currently because there are 3 lines (2, 3, and 10) that fit the criteria of your COUNTIFS - i.e. they all fall within January 2018. If you only want the count to display on rows that fall in January, you can test that condition first - maybe using something like this.

Modified formula:

=IF(AND(A2>=DATE(2018,1,1),A2<=DATE(2018,1,31)),COUNTIFS($C$2:$C$10,C2,$B$2:$B$10, IF(NOT(ISERROR(FIND("Modified",B2))),"Modified*",IF(NOT(ISERROR(FIND("Unable",B2))),"Unable*",B2)),$A$2:$A$10,">="&DATE(2018,1,1),$A$2:$A$10,"<="&DATE(2018,1,31)),0)