SUMPRODUCT - HIERARCHY

[Matt Brown via OfficeKB.com]

The problem i have is this: -

I am using a SUMPRODUCT statement and need to know if you can use hierachy in the conditions and if so how i go about it?

ie. I have 3 conditions ie A, B & C.

A = V8 ENGINE
B = MUSTANG
C = FORD

If A & B are true I want condition A (V8 Engines) to be totalled. If B & C are true I want B (Mustangs) to be totalled.

The problem im having is that if A & B are true, I am getting a total for all vehicles that are MUSTANGS rather than just MUSTANGS with V8 engines. Likewise if B & C are true I am getting a total for all FORDS rather than just FORD MUSTANGS.

*****************************************
* This message was posted via http://www.officekb.com
*
* Report spam or abuse by clicking the following URL:
* http://www.officekb.com/Uwe/Abuse.aspx?aid=2f6c0d3f1277419ea4583ac503e4892a
*****************************************

 

[Bob Phillips]

Not really sure what you want, but you can count the conditions you want
with

=SUMPRODUCT(--(A1:A100="V8 ENGINE"),--(B1:B100="MUSTANG"))

and

=SUMPRODUCT(--(B1:B100="MUSTANG"),--(C1:C100="FORD"))
--

HTH

RP
(remove nothere from the email address if mailing direct)

The problem i have is this: -

I am using a SUMPRODUCT statement and need to know if you can use hierachy

in the conditions and if so how i go about it?

ie. I have 3 conditions ie A, B & C.

A = V8 ENGINE
B = MUSTANG
C = FORD

If A & B are true I want condition A (V8 Engines) to be totalled. If B & C

are true I want B (Mustangs) to be totalled.

The problem im having is that if A & B are true, I am getting a total for

all vehicles that are MUSTANGS rather than just MUSTANGS with V8 engines.
Likewise if B & C are true I am getting a total for all FORDS rather than
just FORD MUSTANGS.

 

[hgrove]

Matt Brown via OfficeKB.com wrote...
...

I am using a SUMPRODUCT statement and need to know if you can us hierachy
in the conditions and if so how i go about it?

ie. I have 3 conditions ie A, B & C.

A = V8 ENGINE
B = MUSTANG
C = FORD

If A & B are true I want condition A (V8 Engines) to be totalled. If & C are true I
want B (Mustangs) to be totalled.

Unclear. First, by 'totalled' do you mean counted? Second, since al
Mustangs are Fords, when would B = Mustang but C <> Ford?

Also, it's always a good idea to show the formula you've tried to us
rather than make everyone else guess where your mistakes may lie.

If you want the count of all V8 Mustangs, use

=SUMPRODUCT(--(A2:A100="V8 ENGINE"),--(B2:B100="MUSTANG"))

If you want the count of all Ford Mustangs, use

=COUNTIF(B2:B100,"MUSTANG")

and check for the validity of your data using

=SUMPRODUCT(--(B2:B100="MUSTANG"),--(C2:C100<>"Ford")

 

[Matt Brown via OfficeKB.com]

Let me try to explain using a different scenario: -

Column A Column B
PRODUCT COVER

*****************************************
* A copy of the whole thread can be found at:
* http://www.officekb.com/Uwe/Forum.aspx/excel-functions/26888
*
* Report spam or abuse by clicking the following URL:
* http://www.officekb.com/Uwe/Abuse.aspx?aid=6b6b170b20e54b79a2b5244e0f9379b8
*****************************************

 

[Matt Brown via OfficeKB.com]

Sorry pressed enter by mistake

This is not easy to explain:

If i use a SUMPRODUCT statement which contains 3 conditions is there any way of assigning a hierachy to the conditions?

ie: -

Col A = Engine size
Col B = Model of vehicle
Col C = Make of vehicle
Col D = Price of vehicle

If I use the following statement -

=SUMPRODUCT(--(((RANGEA1:A10="V8")+(RANGEB1:B10="MUSTANG")+(RANGEC1:C10="FORD"))>0),--(RANGED110)

If the engine size in Column A happens to be V8 and the Model of vehicle in Column B happens to be Mustang I get a price of all Mustang vehicles.

Is there a way of saying ignore Mustang and just give me a price of all V8 engines?

*****************************************
* A copy of the whole thread can be found at:
* http://www.officekb.com/Uwe/Forum.aspx/excel-functions/26888
*
* Report spam or abuse by clicking the following URL:
* http://www.officekb.com/Uwe/Abuse.aspx?aid=f22e2aadcbae47b8b90470e032abf380
*****************************************

 

[Harlan Grove]

This is not easy to explain:

....

Maybe not, but the formula makes it clear.

=SUMPRODUCT(--(((RANGEA1:A10="V8")+(RANGEB1:B10="MUSTANG")
+(RANGEC1:C10="FORD"))>0),--(RANGED110)

Using '+' to combine criteria is the same as OR, so the formula above sums
RANGED1 for all records in which RANGEA is V8, RANGEB is Mustang and RANGEC
is Ford. If all your V8s are Mustangs, this would give the total price of
all Mustangs.

If the engine size in Column A happens to be V8 and the Model of vehicle
in Column B happens to be Mustang I get a price of all Mustang vehicles.

Is there a way of saying ignore Mustang and just give me a price of all
V8 engines?

=SUMIF(A1:A10,"V8",D110)