AND within COUNTIF

[jakrooster]

I have a recruitment database listing details of historical recruitment targets. The database has a column for the job position applied for, the date that their CV was received, the date of interview, the date we made an offer to the recruit, the date their CV was rejected (if it was), and the date that we received a response to our offer.

I'm trying to count how many applicants are "live" i.e. a CV has been received and has not yet been rejected, nor has an offer been accepted or rejected, and count them according to job role. So I want to end up with a table that says: "Admin 3, Manager 0, Finance 5, etc."

My current formula is

=COUNTIFS(Data!C:C,"<>"&"",AND(Data!G:G,Data!I:I),"",Data!J:J,C27)

Column C holds the date of CV received; G is date of rejection, I is date of response, J is job role and C27 refers to a local cell with the required job role.

I get an error message when I attempt to use this formula. My logic is that to count how many "Admin" staff have submitted CVs, there will be an entry in the CV received, i.e. not blank; and that both the date of rejection and response should be blank.

 

[steve the fish]

Hi. The whole point of COUNTIFS is that it allows multiple conditions. You don't need the AND. However what you have said doesn't make sense. You cant use 'column C' for CV received then use C27 to refer to job role. It cant contain both! Ill guess at this:

=COUNTIFS($C$2:$C$20,">0",$G$2:$G$20,"",$I$2:$I$20,"",$J$2:$J$20,$C$27)

 

[jakrooster]

Hi, thanks for reply but as you can see from the formula, C:C is on a different worksheet called "Data".

But your solution of splitting out the AND worked, so many thanks!

Out of interest, I have tried to substitute OR() formula instead of AND as an experiment and the formula also failed.

Am I using AND and OR incorrectly?

 

[steve the fish]

You cant use OR or AND in conjunction with COUNTIFS. They don't work.