Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT - HIERARCHY
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.as...583ac503e4892a ***************************************** |
#2
|
|||
|
|||
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) "Matt Brown via OfficeKB.com" wrote in message ... 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.as...583ac503e4892a ***************************************** |
#3
|
|||
|
|||
Matt Brown via OfficeKB.com wrote... ... 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. Unclear. First, by 'totalled' do you mean counted? Second, since all 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 use 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")) -- hgrove ------------------------------------------------------------------------ hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=275820 |
#4
|
|||
|
|||
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.as...unctions/26888 * * Report spam or abuse by clicking the following URL: * http://www.officekb.com/Uwe/Abuse.as...b5244e0f9379b8 ***************************************** |
#5
|
|||
|
|||
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")+(RANG EC1:C10="FORD"))0),--(RANGED1:D10) 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.as...unctions/26888 * * Report spam or abuse by clicking the following URL: * http://www.officekb.com/Uwe/Abuse.as...0470e032abf380 ***************************************** |
#6
|
|||
|
|||
"Matt Brown via OfficeKB.com" wrote...
.... 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),--(RANGED1:D10) 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",D1:D10) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct on filtered cells | Excel Worksheet Functions | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Shorten sumproduct formula | Excel Discussion (Misc queries) | |||
Optimize SumProduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT using offset from ROW if X marks the spot | Excel Worksheet Functions |