Stumped on "Sumproduct" multi criteria

[Rip-N-Burn]

I have a relatively large workbook with several formul criterias.
This one works fine for only one criteria being hs01 and adds the amounts between 500 and 1000.

=SUMPRODUCT(($C$2:$C$6003="HS01")*($G$2:$G$6003>=500),($G$2:$G$6003<=1000)*$G$2:$G$6003)

My problem is when trying the same with a multi criteria. that being bb02,bb03 and so on.

=SUMPRODUCT(--(C2:C6003={"BB02","BB03","BB04","BB05","BB06","BB07","BB08","BB09","BB10"})--($G$2:$G$6003>=500)--(G2:G6003<=1000))

Any help would be very much appreciated.

Thanks

Kenny..

 

[Domenic]

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH(C2:C6003,{"BB02","BB03","BB04","BB05","BB06","BB07","BB08","BB09","BB10"},0))),--($G$2:$G$6003>=500),--(G2:G6003<=1000))

Hope this helps!

 

[Rip-N-Burn]

Thanks for the fast response.
That formula did not work. The number was added up to 137.00 and should have been like 180,675.00

I'll read the sticky on how to enter the actual formula and sheet onto this board.

 

[Domenic]

The formula I offer counts the number of rows that meet the criteria. If you want to sum Column G according to the criteria, try...

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

Does that help?

 

[Rip-N-Burn]

Totally, uterly amazing!!!! Thanks so much. It worked like a charm!!

I figured out how to use the html maker also.

If you don't mind, can you breakdown the formula in terms a layman could understand. My goal is to understand and be able to help others as well.

Thanks so much again.

Kenny..

 

[Domenic]

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!

 

[Rip-N-Burn]

Thanks again Domenic. I've printed it out for future reference