Need help with Excel formula

[MedX_TB]

I have two sheets in my workbook.

Sheet 1 Title: "At a Glance". This sheet contains:

Column C "Last Name"

Column AF "Do not Recommend".

Sheet 2 Title: "Overall Remarks". This sheet contains:

Column B "last name"

Column C "interview_interview_remarks_0"

Column D "interview_interview_remarks_1"

Column E "interview_interview_remarks_2"

Column F "interview_interview_remarks_3"

Column G "interview_interview_remarks_4"

Column H "interview_interview_remarks_5"

I want cell AF titled "Do not Recommend" on the "At a Glance" sheet to count the number of occurrences from the 'Overall Remarks' tab in rows C through H that have specific text "Interview 1: Do not Recommend" if the data comes from a row with the last name (column B) matching the Last Name (column C) from the 'At a Glance' tab.

I have tried this formula but it's not working: =COUNTIFS('Overall Remarks'!B:B, C2, 'Overall Remarks'!C:C:H:H, "Interview 1: Do not Recommend")

What is the correct formula?

[#2 · Accepted solution]

@MedX_TB 

=SUMPRODUCT(('Overall Remarks'!B:B=C2)*('Overall Remarks'!C:H="Interview 1: Do not Recommend"))

This formula returns the intended result in my Excel 2013 sheet. 

[#3]

You are a genius :) @OliverScheurich