Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct formula help needed
Dear all,
My questions is simple but it seems i can not see the solution: I have 3 columns in an excel sheet 1 is with percentage complete of an activity 2 is with weight (ManHours assigned / Total ManHours) 3 is the activity name Now i need to put all activities under a certain name, summed in 1 cell example: Description1 actual % complete weight/desc Under Ground Concrete Work 100% 1.3% Under Ground Concrete Work 100% 5.1% Under Ground Concrete Work 100% 0.7% Under Ground Concrete Work 100% 5.0% Under Ground Concrete Work 100% 4.6% Above Ground Concrete Work 100% 49.4% Above Ground Concrete Work 100% 50.6% Now i need to summup under a cell called underground concrete, column called AREA1, all under ground concrete by the result of each (actual % complete * weight/desc) like (100% * 1.3%) + (100% 5.1% ) + (100% 0.7%) etcetera ... what i found that it should be done by sumproduct but how, i cant figure out could you please inform me? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct formula help needed
If you are saying that you want the sum for all ugcw that is 100% then
=sumproduct((a2:a22="under groud concrete work")*(b2:b22"=1)*c2:c22) you may not use entire columns and the ranges must be the same size On Oct 16, 3:59*am, Totti wrote: Dear all, My questions is simple but it seems i can not see the solution: I have 3 columns in an excel sheet 1 is with percentage complete of an activity 2 is with weight (ManHours assigned / Total ManHours) 3 is the activity name Now i need to put all activities under a certain name, summed in 1 cell example: Description1 * * * * * * *actual % complete *weight/desc Under Ground Concrete Work * * *100% * *1.3% Under Ground Concrete Work * * *100% * *5.1% Under Ground Concrete Work * * *100% * *0.7% Under Ground Concrete Work * * *100% * *5.0% Under Ground Concrete Work * * *100% * *4.6% Above Ground Concrete Work * * *100% * *49.4% Above Ground Concrete Work * * *100% * *50.6% Now i need to summup under a cell called underground concrete, column called AREA1, all under ground concrete by the result of each *(actual % complete * weight/desc) like (100% * 1.3%) + (100% * *5.1% * *) + (100% 0.7%) etcetera ... what i found that it should be done by sumproduct but how, i cant figure out could you please inform me? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct formula help needed
Not really 100% but any other value may apply, just because the
progress is entered by a Data Entry Operator is by chance 100% but it can range anywhere from 0 to 100 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct formula help needed
On Oct 16, 8:16*am, Totti wrote:
Not really 100% but any other value may apply, just because the progress is entered by a Data Entry Operator is by chance 100% but it can range anywhere from 0 to 100 Then, I don't understand your need. Send your file with a complete explanation and before/after examples to dguillett1 @gmail.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct formula help needed
Hi Totti,
Am Sun, 16 Oct 2011 06:16:55 -0700 (PDT) schrieb Totti: Not really 100% but any other value may apply, just because the progress is entered by a Data Entry Operator is by chance 100% but it can range anywhere from 0 to 100 Description in Col A, actual % in Col B, weight/desc in Col C. Sort by Col A and then in D2: =IF(A2=A3,"",SUMPRODUCT(--($A$2:$A$100=A2),$B$2:$B$100,$C$2:$C$100)) and drag down. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct formula help needed
"Totti" wrote:
Now i need to summup under a cell called underground concrete, column called AREA1, all under ground concrete by the result of each (actual % complete * weight/desc) like (100% * 1.3%) + (100% 5.1% ) + (100% 0.7%) Suppose your data are in rows 2 through 10 in columns A, B and C. Then: =sumproduct((A2:A10="Under Ground Concrete Work")*B2:B10*C2:C10) ----- original message ----- "Totti" wrote in message ... Dear all, My questions is simple but it seems i can not see the solution: I have 3 columns in an excel sheet 1 is with percentage complete of an activity 2 is with weight (ManHours assigned / Total ManHours) 3 is the activity name Now i need to put all activities under a certain name, summed in 1 cell example: Description1 actual % complete weight/desc Under Ground Concrete Work 100% 1.3% Under Ground Concrete Work 100% 5.1% Under Ground Concrete Work 100% 0.7% Under Ground Concrete Work 100% 5.0% Under Ground Concrete Work 100% 4.6% Above Ground Concrete Work 100% 49.4% Above Ground Concrete Work 100% 50.6% Now i need to summup under a cell called underground concrete, column called AREA1, all under ground concrete by the result of each (actual % complete * weight/desc) like (100% * 1.3%) + (100% 5.1% ) + (100% 0.7%) etcetera ... what i found that it should be done by sumproduct but how, i cant figure out could you please inform me? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct formula help needed
Joeu2004,
Thank you, That did the job Thanks a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and SUMPRODUCT help needed | Excel Discussion (Misc queries) | |||
Min/Max help needed with a SumProduct | Excel Worksheet Functions | |||
Sumproduct Help Needed | Excel Discussion (Misc queries) | |||
SUMPRODUCT help needed | Excel Worksheet Functions |