Sum Multiple Criteria

[JCS]

Hello Everyone,

I have been try to learn how to use the SUM function with multiple criteria.
I have run into a road block with my experimentation. By the way, I am
using Excel 2003 SP3. My data looks like the following:

city Mfr model volume
dearborn ford taurus 10
troy ford fusion 20
shelby gm lacrosse 10
warren chrysler sebring 10
dearborn gm camaro 30
detroit ford fusion 40
shelby chrysler magnum 20
warren chrysler avenger 40
troy chrysler avenger 20
detroit gm lacrosse 50
detroit gm impala 30
warren ford fusion 40
troy chrysler avenger 20
detroit ford taurus 10

The formula I am having a problem with is:

{=SUM(($C$5:$C$18="taurus")+($C$5:$C$18="fusion")+($D$5:$D$18))}

The Sumprduct yields 120 as the answer which is correct but the formula
above yields 355 as the answer. What am I missing?

Thanx in advance,
John

 

[Don Guillett]

=SUMPRODUCT((C5:C18={"taurus","fusion"})*D518)
Need NOT be array entered

 

[Jacob Skaria]

'array entered
=SUM((ISNUMBER(SEARCH({"taurus","fusion"},$C$5:$C$18)))*$D$5:$D$18)

OR
=SUMPRODUCT((ISNUMBER(SEARCH({"taurus","fusion"},
$C$5:$C$18)))*$D$5:$D$18)

If this post helps click Yes

 

[JCS]

Thank you Jacob,

So, according to your solution, my solution is not possible.

John

 

[Jacob Skaria]

What your formula does is

=sum(count number of 'taurus'+ count number of 'fusion' + the array D518)
=sum(2+3+350)
=355

If this post helps click Yes

 

[JCS]

Thanks again Jacob,

You gave me exactly what i was looking for. I was looking for the why my
formula was not working. Thank you so much!

John

 

[WKH8919]

What your formula does is

=sum(count number of 'taurus'+ count number of 'fusion' + the array D518)
=sum(2+3+350)
=355

If this post helps click Yes

Simple way is

=SUMIF(C5:C18,"taurus",D518)+SUMIF(C5:C18,"fusion",D518)

Worawat

 

[JCS]

Many thannks Don. Your help is greatly appreciated.

John