SUMPRODUCT WITH COUNTIFS for MULTIPLE WORKSHEETS with one criteria

[Derick63]

Hello Everyone,

I can't seem to figure this one out and I've looked around for an answer to no avail.

I have 13 sheets, 12 of them, January through December, have the same layout and formatting.
I have a named range of the months called Sheets on a sheet called Helper Sheet

The first sheet, TD4 Data, in cell Y2, I'm looking to find the employees name, listed in A2, in all sheets within the range C9:C133 then count the number of times 41.25 and 33.00 in range L9:L133 in the 12 sheets for the employee in A2.

Here's what I have but producing a #Value in Y2. Would I need to use an array somewhere in the formula?

Thanks in advance

=IF($A2="","",SUMPRODUCT((COUNTIFS(INDIRECT("'"&Sheets&"'!$L$9:$L$133"),"41.25"))+(COUNTIFS(INDIRECT("'"&Sheets&"'!$L$9:$L$133"),"33")),INDIRECT("'"&Sheets&"'!$C$9:$C$133"),$A2))

I also tried this and got the same #Value

=IF($A2="","",SUMPRODUCT((COUNTIFS(INDIRECT("'"&Sheets&"'!$L$9:$L$133"),{41.25,33})),INDIRECT("'"&Sheets&"'!$C$9:$C$133"),$A2))

 

[Domenic]

You were close...

=IF($A2="","",SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!$L$9:$L$133"),{41.25,33},INDIRECT("'"&Sheets&"'!$C$9:$C$133"),$A2)))

Hope this helps!

 

[Derick63]

You were close...

=IF($A2="","",SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!$L$9:$L$133"),{41.25,33},INDIRECT("'"&Sheets&"'!$C$9:$C$133"),$A2)))

Hope this helps!

Hey Domenic. That worked. Thanks so much.

**** Brackets!!!!!!