Excel counting certain cell range in row if other cell matches column

Question

So I inherited an Excel file that is used to schedule and track PTO and OT time for about 100 employees. They have a column for every day of the year and have the year split over two sheets.

I am trying to create a totaling sheet that shows a grouping for each employee and counts up each of the types of time codes that are used in the tracking sheets. Each time type has a code that is used for condidtional formating NM1, NM2,...

I am trying to create a formula that will check the employee's name against the name column on the tracking sheet and then count just part of the row for cells that contain NM1, etc

Here is one of the cells as it is now:

=COUNTIF('2015MarNov'!$E$88:$AH$88,"*"&"NM1"&"*")

the employee name is in column D and this is counting NM1 for just January (columns E through AH).

So you want to add to the existing formula to also include a criteria for the employees name? — Scott Craner, Feb 04 '16 at 19:14
I'm trying to see if there is a simpler way to construct this to avoid having to cut and paste it 100+ times and changing the row reference with find/replace — Matt Fr, Feb 04 '16 at 19:23
Mock up some data and post it in your original post so we do not have to create our own to test. — Scott Craner, Feb 04 '16 at 19:26
https://docs.google.com/spreadsheets/d/1CbLsekrM2Em5NQ4ibg5MjtNpeJ0rDgsK5w4wVTcIMyY/edit — Matt Fr, Feb 05 '16 at 16:02

score 0 · Answer 1 · edited Feb 13 '16 at 16:33

Try this:

=COUNTIF(OFFSET($E$1:$AH$1,MATCH(<target employee cell>,$D:$D,0)-1,0),"*NM1*")

In this example I assumed that row 1 contains the header and that row 2 starts with the employees. If that is not the case try something like this:

=COUNTIF(OFFSET($E$5:$AH$5,MATCH(<target employee cell>,$D$6:$D$106,0),0),"*NM1*")

Edit, explanation:

Match(<target employee cell>,<range of employees>,0)

This function returns the relative row in which the target employee is found, i.e. if the employee is in D7 and the range is D6:D106 then the returned value is 1, since this the relative offset from the starting range (starting at 0)

OFFSET(<range>,<rows>,<columns>)

This function shifts any given range by the number of rows and columns as specified. In the previous function the range is offset by 1 row (shifted 1 row down).

COUNTIF(<range>,<criteria>)

The <range> is determined by the shifted range from the OFFSET function.

`OFFSET()` is a [volatile function](https://msdn.microsoft.com/en-us/library/office/bb687891.aspx), in that it will recalculate all cells everytime there is a change, regardless if it is data that effects the formula or not. Depending on how many of these formulas the OP will place on the sheet it will greatly slowdown anytime a change is made. — Scott Craner, Feb 04 '16 at 19:43
Here is a link to a section of the document in question https://docs.google.com/spreadsheets/d/1CbLsekrM2Em5NQ4ibg5MjtNpeJ0rDgsK5w4wVTcIMyY/edit?usp=sharing — Matt Fr, Feb 04 '16 at 21:10

score 0 · Answer 2 · answered Feb 04 '16 at 19:39

Use this:

=COUNTIF(INDEX('2015MarNov'$E:$E,MATCH("NAME",'2015MarNov'$D:$D,0)):INDEX('2015MarNov'$AH:$AH,MATCH("NAME",'2015MarNov'$D:$D,0)),"*"&"NM1"&"*")

Change the "NAME" to the cell on the summary sheet the has the Employee name. You could also change the "NM1" to a cell reference.

Excel counting certain cell range in row if other cell matches column

2 Answers2