INDIRECT function works with COUNTIF but not with SUMPRODUCT

[Mariner]

Hi,

I'm trying to use the indirect function to link to branch tabs throughout my file.
Count column = +COUNTIF(INDIRECT("'"&$A8&"'!$C$6:$C$400"),$C$4)/5. This works for each branch (35, 12, 9, and so on).

Hours column = +SUMPRODUCT(--(Houston!$C$6:$C$400=$C$4),--(Houston!$F$6:$F$400=D$5),Houston!$BG$6:$BG$400). That works fine for Houston (I have that copied down for the Hours column for now; that's why the number is the same for each branch).

I want to change the hours column formula so that I can copy it down without changing the branch name every time so I used:
+SUMPRODUCT(--(INDIRECT("'"&$A8&"'!$C$6:$C$400)=$C$4),--(Houston!$F$6:$F$400=D$5),Houston!$BG$6:$BG$400) just on the first leg of the formula and I get the pop up box with "There's a problem with this formula..."

Can SUMPRODUCT and INDIRECT work together? If so, what is the fix? Thanks in advance!

 

[Fluff]

You're missing the closing quotes at the end of the range in the indirect function.

 

[Mariner]

Great catch. It works. Eureka! TY!

 

[Fluff]

You're welcome & thanks for the feedback.