Stumped on "Sumproduct" multi criteria

Rip-N-Burn

New Member
Joined
Apr 27, 2005
Messages
18
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..
 

Some videos you may like

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
Joined
Mar 10, 2004
Messages
19,403
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
Joined
Apr 27, 2005
Messages
18
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
Joined
Mar 10, 2004
Messages
19,403
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
Joined
Apr 27, 2005
Messages
18

ADVERTISEMENT

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
Joined
Mar 10, 2004
Messages
19,403
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,251
Members
412,709
Latest member
Rishu
Top