CODEWITHSUNDEEP
excelexcel-formula
Mabes
Mabes

Reputation: 25

count values in a row based on criteria in a column excel

So this problem I thought could be solved with a Countif - but that function requires a 'square' array to look at. I have a list of staff names(Column with 28 rows), and a calendar with a shift type (365 columns, 10 shift types). What I am trying to do is count the instances of each shift type in a row, but keeping the row dynamic based on the staff person name. So if Bob is row 5 it will count his "D" shifts, but if I later move him to row 7 it will stay with counting his shifts. So Staff names in Column A5:A28, Shift types in Range B5:ND28. The summary page will move around staff names from row to row so I need to be able to count shift types based on the name in Column A and match the row with that value. I am too much of a noob to embed images...

Upvotes: 0

Views: 1646

Answers (1)

tigeravatar
tigeravatar

Reputation: 26660

Assuming a data setup like this:

enter image description here

Column L contains the name you're looking for from your grid, column M contains the Shift Type you want to count for that name, cell N2 contains this formula to get the count and is copied down:

=COUNTIF(INDEX(B:J,MATCH(L2,A:A,0),0),"*"&M2&"*")

Upvotes: 2

Related Questions