Wildcard use with COUNTIFS and alphanumeric search term?

[rjyusmc2005]

Is there a more efficient way to do this, wildcards, different formula all together? Or is this just wishful thinking?
This is a condensed version of the formula I'm trying to streamline:

=G4-((COUNTIFS(B9:Y39,"S1")*1)+(COUNTIFS(B9:Y39,"S2")*2)+.......(COUNTIFS(B9:Y39,"S8")*8))

The "S#" value ranges from S1 to S8 and I currently have this string repeating for each potential value, which makes for a lengthy formula. The number (2 in S2) is also the value it needs to be multiplied by. Is there a way to extract that value and have it used as the multiplier? Count the cells that contain "S#", take the numerical value from that cell, "2" for example, and multiply the number of cells containing "S2" by that value. If there are 15 instances of S2, it should give a result of 30. This would need to be able to work for any value after the "S". If it's "S4" it would be multiplied by 4, "S6" would be multiplied by 6.

 

[Fluff]

Hi & welcome to MrExcel.
You can do that like

=SUM(COUNTIFS(B9:Y39,{"S1","S2","S3"})*{1,2,3})

just expand the two arrays.