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?

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?

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

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

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

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?

Sumproduct formula help needed
 

Joeu2004,
Thank you, That did the job
Thanks a lot