Sumproduct with countif in multiple worksheets

[Derick63]

Hello All,
I have a Payroll workbook with 13 worksheets, January through December, and another sheet named "TD4" to display collected data in a form to print.
The 12 months all have the same layout and formulas.

I'm looking to count how many times $8.25 is entered in Column P for an employee's name in column C across the 12 sheets.

• The TD4 sheet has the employee's name in cell C3.
• All employee names appear in range C9:C109 in all 12 sheets. Each employee will appear four or five times in the column.
• The $8.25 would be in range P9:P109 in all 12 sheets.
• I have a named range (column) with the month names, January through December, called sheets.

I started using =COUNTIF(INDIRECT("'"&Sheets&"'!$C$9:$C$109"),$C$3) just to count the employee's name but I'm getting 4 as the product but I know there's 48 instances across all 12 sheets.

I also tried this but I know it's not right because I'm getting a #VALUE error =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!$C$9:$C$109"),$C$3)+(INDIRECT("'"&Sheets&"'!$P$9:$P$109")="8.25"))

I hope I explained this issue properly......

Getting frustrated with this.....
Any help would be great. Thanks in advance.

 

[Derick63]

I also tried =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!$C$9:$C$109"), C3),COUNTIF(INDIRECT("'"&Sheets&"'!$C$9:$C$109"), 8.25)) and the result was 0.
There are 48 instances of 8.25 for the employee named in cell C3 on the TD4 worksheet.

 

[Derick63]

SOLVED!!!!!

I used COUNTIFS and reversed the array and criteria.

Corrected formula:
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!$p$9:$p$109"),"8.25",INDIRECT("'"&Sheets&"'!$C$9:$C$109"),$C$3))