COUNTIF embedded in a SUMPRODUCT formula

Good evening -
I have a COUNTIF formula embedded in my SUMPRODUCT formula, and all I get is the #VALUE! output. The COUNTIF works on its own, but it won't work embedded. My challenge is to use the SUMPRODUCT where one of the two Arrays is a word (it's text and not a number). The other Array is a number. As I type I'm thinking I need to convert the text to a number, based on the word. I think I'm confusing myself with the syntax and right way to build the formula. Thanks for any help, in advance.

The full formula (as I have it now):

Please Login or Register  to view this content.

You're right about needing a sample worksheet, to properly explain my challenge. I created one, and simplified the code so that there weren't references to other sheets in the book. The code is probably easier to follow, with it being simplified. But it still doesn't work. I think the attached sample highlights my challenge. Thank you!

Wow, daddylonglegs - that worked!! I was making it too complicated. Thank you very much for your help. Have a nice day.