As an example, let's assume the following...

C1:C5 contains the following values:

{BB04;CC05;BB08;DD10;BB02}

G1:G5 contains the following values:

{750;850;475;1200;975}

If we have the following formula...

=SUMPRODUCT(--(ISNUMBER(MATCH($C$1:$C$5,{"BB02","BB03","BB04","BB05","BB06","BB07","BB08","BB09","BB10"},0))),--($G$1:$G$5>=500),--($G$1:$G$5<=1000),$G$1:$G$5)

...here's how it breaks down...

MATCH($C$1:$C$5,{"BB02","BB03","BB04","BB05","BB06","BB07","BB08","BB09","BB10"},0) evaluates to:

{3;#N/A;7;#N/A;1}

ISNUMBER(MATCH($C$1:$C$5,{"BB02","BB03","BB04","BB05","BB06","BB07","BB08","BB09","BB10"},0)) evaluates to:

{TRUE;FALSE;TRUE;FALSE;TRUE}

--(ISNUMBER(MATCH($C$1:$C$5,{"BB02","BB03","BB04","BB05","BB06","BB07","BB08","BB09","BB10"},0))) evaluates to:

{1;0;1;0;1}

Notice that the double negative coerces TRUE and FALSE to their numerical equivalent 1 and 0, respectively.

--($G$1:$G$5>=500) evaluates to:

{1;1;0;1;1}

--($G$1:$G$5<=1000) evaluates to:

{1;1;1;0;1}

$G$1:$G$5 evaluates to:

{750;850;475;1200;975}

SUMPRODUCT multiplies these arrays and sums the results. So here's what we have...

=SUMPRODUCT({1;0;1;0;1},{1;1;0;1;1},{1;1;1;0;1},{750;850;475;1200;975})

...which gives us...

=SUMPRODUCT({750;0;0;0;975})

...and returns 1725.

Hope this helps!