bj wrote:
[...]
try

=sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

or
=sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

I am not sure if this answers your second quesiton but the style should help
you figure out what to do for the third and fourth questions.

You can't call on OR (or AND) in a formula that needs to operate on
evaluations that are arrays, not scalars (single values).

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.