# Stumped on "Sumproduct" multi criteria

#### Rip-N-Burn

##### New Member
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..

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Domenic

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member

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

##### MrExcel MVP
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

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

Replies
12
Views
692
Replies
10
Views
372
Replies
3
Views
3K
Replies
7
Views
548
Replies
3
Views
2K