How can I use COUNTIFs or VBA to look through 3 columns and count the values in a row?-CodePudding

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.

CodePudding user response：

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

The formula would be

=NUMBERVALUE(SUBSTITUTE(INDEX(B2:B5,MATCH("*"&B10&" Over*",B2:B5,0)),B10&" Over",""))

CodePudding user response：

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