Excel: Combine =vlookup and =countif

Question

For my question I am trying to reduce a very large amount of data using the =countif function in regards to a specific Employee ID (using =vlookup).

In Column 'A' I have every employee ID listed only once. In columns B, C, D, E, and F I would like to count every time that employee has been Hired, Promoted, received a Pay Increase, been Demoted and Fired, respectively.

In Column 'I,' I have again a list of employee ID's and in 'J' each time one of those actions were implemented. Since there are more than 10,000 employee iterations that exist in column 'I' this is why I am trying to condense these down to numeric values in columns B:F.

ACTUAL QUESTION: Is there anyway to 'nest' these two functions in order to get the required results that I want?

Thanks in advance.

Answer 1

You can use Countifs with multiple conditions (not Countif, which takes only one condition)

Consider the following screenshot. The formula in cell B2 is

=COUNTIFS($I:$I,$A2,$J:$J,B$1)

Copy across and down. Note the position of the $ signs. They are important. The column references for columns I and J are absolute, and will not change when the formula is copied across. The reference to $A2 will always refer to column A, but the row will adjust when copied down. The reference to B$1 will always refer to row 1, but the column will adjust when the formula is copied across.

You can do a similar thing without any formulas at all, using a pivot table. Click a cell anywhere in the data in columns I or J, then click Insert > Pivot Table. In the pivot table pane that appears on the right, drag the Employee ID to the Rows area, drag the action to the Columns area and drag either of the fields to the Values area. The result looks like this:

Look Ma, no formulas!!