Formula to count how many cells have 2 diff variables

[mmmsoupy]

My work has a "Termination Report" where we keep track of how many employees quit/were fired for whatever reason. I'd like for the cells to automatically count this for me. This is how we have it set up:

The list of stores are in Column A on the left and the list of reasons are at the top in Row 2.

I've been keeping track of all terminated employees on the other sheet which looks like this:

On this sheet the E column contains the "Reason for termination" code

So using one cell as an example: on the "Central Valley" sheet C3 is the cell for how many terms from Arden Fair (Store #15) were because of unsatisfactory performance. I would want the formula to count how many times "15" appears in the B column (On the "Employees" worksheet) and "T01" appears in the E column at the same time. (T01 is the term code for "Unsatisfactory Performance")

I think I'll probably have to add in a column out of print range (or hidden) to put a "1" in each row so it has something to count... And that's fine.

I really hope this makes sense... because this is for the whole year and if I have to go back through and count each of these individually for all of 2011 through August, I think I may cry.

 

[AlphaFrog]

If you have Excel 2007 or later, you could use the COUNTIFS function

=COUNTIFS(Employees!B:B, 15, Employees!E:E, "T03")


If you have Excel 2003 or earlier, use the SUMPRODUCT function.

=SUMPRODUCT(--(Employees!B2:B500=15),--(Employees!E2:E500="T03"))

 

[mmmsoupy]

Thank you so much! Although if anyone else ever needs something like this I did have to modify the formula slightly.

<table width="33" border="0" cellpadding="0" cellspacing="0"><col width="33"><tbody><tr height="40"> <td class="xl22" style="height: 30pt; width: 25pt;" width="33" height="40">=SUMPRODUCT(--('Employees'!B3:B600=15),--('Employees'!E3:E600="T01"))</td></tr></tbody></table>