SUMPRODUCT, COUNTIF formulas not correctly counting based on two sets of criteria

I've attached an Excel file to this post. For some reason, I can't find the right formula (i.e. I've tried SUMPRODUCT, COUNTIF, and other combinations) to correctly account the number of instances of a value appearing, based on satisfying two sets of criteria indexed in other columns. I'm thinking it's just a minor tweak of something that I missed. The attached file provides a lookup table, several of my attempts to solving the problem, and a restatement of my question with desired results versus actual results. Any help would be much appreciated.