SUMIF with multiple criteria, one criteria a specific string

So I've trying to create a formula that will mimic a sumif function but using multiple criteria. One of the criteria is to look for a certain character string that could be inside of a cell that contains other characters as well. I know if I do a sumif function I can use a formula like this:

=SUMIF(Sheet1!$B:$B,"*" & "Test" & "*",Sheet1!$F:$F)

That would look in row B for any cells that had the string Test anywhere inside the cell and then sum up column F. My problem is that I need a formula that will look for a certain string as well as make sure it matches another value. I've tried using this formula:

=SUMPRODUCT(--(Sheet1!B1:B100="*" & "Test" & "*"),--(Sheet1!C1:C100=20),Sheet1!F1:F100)

That is looking in column B for a string of Test and then also making sure that column C = 20. Fopr any matches it sums up column F. The formula doesn't work though because it doesn't allow the function I have setup to look for Test. Can anyone help me with a solution. Thanks

I've attached the file. There are two sheets in the workbook. The first has the raw data while the second is the reporting page with the formula's. The first criteria is all listed in Row 1 while the second criteria is listed in Column A.

That worked but I ran into one problem. In the file I'm working on there are cells that contain numbers and other cells that contain text. When I use the formula use listed it won't work unless I delete all of the text cells in column F. Is there a way to tweak the formula so it would work with text valued cells still in column F?

Also, if I wanted to change the second criteria (i.e. Sheet1!$B$1:$B$17=$A2) so that instead of looking for a specific value listed in cell A2 it looked for a value range. For example, instead of looking for a match of the value 85 it looked for a match in which the value could be between 80 and 90.

Thanks in advance for any help