Count value on each sheet across workbook

[Dublin Irish]

Hello,

Im a complete novice so please bear with me ,

Im looking to count the number of times a word is used on each sheet across my workbook. I have created a list of sheet name and the criteria and tried using the following function to no avail.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Table4[[#Headers],[Sheets]]&"'!C7:C33"),C4))

I should be getting 2 as my answer

Thanks In Advance

 

[Fluff]

You don't want to include the header in the formula, so try it like

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Table4[Sheets]&"'!C7:C33"),C4))