0

The data set that I have has multiple instances of names and employee numbers. I am looking to find the instance where the employee's id number, name and category line-up then count the values in the row.

for example.

                     1/01  1/02   1/03   01/04   01/05
12345 Daniel Start      7      2      1       7      3
12345 Daniel Break      30     1      1       1      30
12345 Daniel End        12     8      7       12     10
12345 Daniel Over 4     0      1      1       0      1
12345 Daniel Total Hr's 4.30   5      5       4.30   6  


**If I want to find how many times Daniel worked over 4 hours ( 12345 Daniel Over 4 ) how can I use Excel to count the number of 1's in the row where Daniel is over 4?**



Extra Notes: I'm open to trying VBA or formula's...I'm stumped.
YANEAL LARIAM
  • 1
  • The labels are standard? This means after the employee name there's always "Start" "Break" "End" "Over" and these exist in the dataset. I assume that "Total Hr's" is the one that you wish to calculate, am I correct? – Sgdva Jan 26 '22 at 15:11
  • Yes after each employee they're labeled that way. I'm most interested in counting up the values in the " Over 4" row. – YANEAL LARIAM Jan 26 '22 at 15:24

3 Answers3

0

If you're using VBA, you don't need to restrict yourself to CountIfs limited abilities, you can loop through every row and count up the values with your own custom script. Get the last row of the sheet with this technique and then you can loop through the sheet like:

Sub Example()
    Dim SearchValue1 As String, SearchValue2 As String, SearchValue3 As String
    SearchValue1 = "12345"
    SearchValue2 = "Daniel"
    SearchValue3 = "End"
    
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long, SearchCount As Long
    For i = 1 To LastRow
        If ws.Cells(i, 1) = SearchValue1 _
            And ws.Cells(i, 2) = SearchValue2 _
            And ws.Cells(i, 3) = SearchValue3 _
        Then
            SearchCount = SearchCount + WorksheetFunction.CountIf(ws.Rows(i), "<>") - 3
        End If
    Next

    Debug.Print SearchCount
End Sub

This could also be turned into a User Defined Function that accepts the SearchValues and the search range as arguments, and then returns the SearchCount.

Move the code into a code module, and modify it like so:

Function CustomCount( _
                     SearchRange As Range, _
                     SearchValue1 As String, _
                     SearchValue2 As String, _
                     SearchValue3 As String _
                    )
    Dim ws As Worksheet
    Set ws = SearchRange.Parent
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long, SearchCount As Long
    For i = 1 To LastRow
        If SearchRange.Cells(i, 1) = SearchValue1 _
            And SearchRange.Cells(i, 2) = SearchValue2 _
            And SearchRange.Cells(i, 3) = SearchValue3 _
        Then
            SearchCount = SearchCount + WorksheetFunction.CountIf(ws.Rows(i), "<>") - 3
        End If
    Next

    CustomCount = SearchCount
End Function

As a UDF you can use it inside your Excel formulas like =CustomCount(A:C,"12345","Daniel","End")

This UDF could be further improved by adjusting the code to allow a variable number of SearchValues. You could make some optional, or you could make them into a ParamArray. But I will not put that in this answer and leave that for you to explore if you are interested.

Toddleson
  • 4,321
  • 1
  • 6
  • 26
0

If you need a VBA approach, I would tweak Toddleson answer, but according to your comments, it may not be necessary and only an index/match will be able to satisfy the scenario stated:

enter image description here

The formula would be

=NUMBERVALUE(SUBSTITUTE(INDEX(B2:B5,MATCH("*"&B10&" Over*",B2:B5,0)),B10&" Over",""))
Sgdva
  • 2,800
  • 3
  • 17
  • 28
  • Yes this helps! But the name and the other labels are separated from each other into two columns like: Daniel Start 7 – YANEAL LARIAM Jan 26 '22 at 20:06
  • @YANEALLARIAM It is even easier that way, take a look into [this](https://exceljet.net/formula/index-and-match-with-multiple-criteria) – Sgdva Jan 26 '22 at 20:57
0

If you dispose of the newer dynamic array features of Excel 2021+/MS Excel 365, you might procede as follows using a tabular formula input:

  • Filter() the numeric data columns (e.g. D:H),
  • connect all three filter conditions (id in column A:A, name in col B:B, type in col C:C) as logical And by multiplying (A:A=12345)*(B:B="Daniel")*(C:C="Over 4") to get one boolean result and
  • execute a simple Sum() upon the filtered row(s):
    =SUM(FILTER(D:H,(A:A=12345)*(B:B="Daniel")*(C:C="Over 4")))

Note: Of course you can replace any explicit search value also by sheet references getting sheet inputs of the search criteria.

Example: assuming an id input e.g. in cell B2 of Sheet1: =SUM(FILTER(D:H, (A:A=Sheet1!B2)* ... )).

In case you dispose of the Let() function allowing to organize inputs systematically, you might also enter the following formula:

    =LET(data,D:H,id,12345,name,"Daniel",type,"Over 4",SUM(FILTER(data,(A:A=id)*(B:B=name)*(C:C=type))))

Further hint: You might want to have a look at the advanced Lambda() function, too which by now is only available to members of the Insiders Beta program - see Lambda - the ultimate excel worksheet function

T.M.
  • 9,436
  • 3
  • 33
  • 57