Need to call up info from cells based on values from another

So, here's a sample of a spreadsheet to give you an idea for what I'm dealing with.

NeedHelp.PNG

\1

What I want to do is create a formula that will show how many people left a dept during a certain period of time. Maybe I want to see how many workers left the "Home" department during January 2016. Is there a way that I can make a formula that says to check if a range has any cells that say "Home" and then if so, it could check another range to see if the date is between 1/1/16 and 1/31/16 (but only for the cells whose row had the "Home" value), and then return a count of that number. What about if I wanted to see how many had left in the last 30 days? I know there's a TODAY() thing you can use for formulas like that, right?

Like, I can get it to count how many cells say "Home". And I can get it count how many cells have a value between 1/1/16 and 1/31/16. But I can't figure out how to get a formula that returns a count only when both criteria are met.

Any help would be appreciated, thanks!!

Thank you, that seems to be working!

How would I set it up so that it returns values from the last 30 days only? Like, I only want to see how many people from "Home" have left in the last 30 days? I know it will probably involve that TODAY() thing.

Maybe this...

=COUNTIFS(F:F,"Home",C:C,">="&TODAY()-30)

It means greater than or equal to.

In most other functions we can just do this:

=IF(A1>=TODAY()-30...

However, in the COUNTIF(S) function we have to use quotes and concatenate when used with cell addresses or other functions:

=COUNTIFS(...,">="&TODAY()-30)

=COUNTIF(A:A,">="&B1)

Yep, that's right.

One of Excel's many quirks!