Multiple Range and Multiple Criteria in a COUNTIF

[KADean]

I am working in a workbook where the formulas reference between two worksheets. I am trying to count the number of candidates that are applying for the same job requisition number and are in active status. The job requisition number can be found in column A and the Active or Inactive Status can be found in column J. Here is the formula that I am using....

=COUNT(IF(('Candidate Summary'!$J$3:'Candidate Summary'!$J$77),"Active", IF(('Candidate Summary'!$A$3:'Candidate Summary'!$A$77),'Current Opening Summary'!A3)))

However, it just returns a 0 value.

Anyone have any guidance?

[Trebor76]

Hi there,

Bit hard to say without seeing the data, but hopefully the following formula should do the trick:

COUNTIF('Candidate Summary'!J3:J77,"Active")+COUNTIF('Candidate Summary'!A3:A77,"Current Opening Summary")

HTH

Robert

[KADean]

Thank you for the suggestion, however, this is still not working.

I have attached the spreadsheet in a zipped format.

I have become so frustrated with this report, I'm really beginning to think this is not a conceivable approach....

Any suggestions?????

[NBVC]

Try (in the "Current Opening Summary" sheet)

=SUMPRODUCT(--('Candidate Summary'!$A$3:$A$77=A3),--('Candidate Summary'!$J$3:$J$77="Active"))

adjusting ranges to suit and copy formula down.

[KADean]

That was beautiful!!!! Perfection even!!!!

Thank you so much!!!!