Sumproduct formula help needed

  • Thread starter Thread starter Totti
  • Start date Start date
T

Totti

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?
 
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
 
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
 
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
Click to expand...

Then, I don't understand your need.

Send your file with a complete explanation and before/after examples
to dguillett1 @gmail.com
 
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
Click to expand...

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
 
Totti said:
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%)
Click to expand...

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 -----
 
Back
Top