SUM with condition

[Thiyagu]

NCT1 Qty NCT2 Qty NCT3 Qty NCT1 Hrs NCT2 Hrs NCT3 Hrs Total Hrs
1 1 2 3 1 only sum for coloum have no
2 1 2 3 2 only sum for coloum have no
2 1 1 2 3 5 only sum for coloum have no

Anyone can help me , total have 3 column for qty & 3 column for hrs..
Only sum the hrs for column that have value at qty column...

Thanks in advance

 

[Thiyagu]

not so clear the pic...reattach back
<table x:str="" style="border-collapse: collapse; width: 575pt;" border="0" cellpadding="0" cellspacing="0" width="766"><col style="width: 63pt;" span="3" width="84"> <col style="width: 61pt;" span="3" width="81"> <col style="width: 60pt;" width="80"> <col style="width: 143pt;" width="191"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt; width: 63pt;" height="18" width="84">NCT1 Qty</td> <td class="xl25" style="border-left: medium none; width: 63pt;" width="84">NCT2 Qty</td> <td class="xl25" style="border-left: medium none; width: 63pt;" width="84">NCT3 Qty</td> <td class="xl26" style="border-left: medium none; width: 61pt;" width="81">NCT1 Hrs</td> <td class="xl26" style="border-left: medium none; width: 61pt;" width="81">NCT2 Hrs</td> <td class="xl26" style="border-left: medium none; width: 61pt;" width="81">NCT3 Hrs</td> <td class="xl26" style="border-left: medium none; width: 60pt;" width="80">Total Hrs</td> <td class="xl27" style="width: 143pt;" width="191">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl28" style="height: 13.5pt; border-top: medium none;" height="18">　</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="1">1.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="2">2.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="3">3.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="3" x:fmla="=3">3.00 </td> <td class="xl27">only sum for coloum have no</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl28" style="height: 13.5pt; border-top: medium none;" height="18">　</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">　</td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="1">1.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="2">2.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="3">3.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="2">2.00 </td> <td class="xl27">only sum for coloum have no</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl28" style="height: 13.5pt; border-top: medium none;" height="18">　</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="1">1.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="2">2.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="3">3.00 </td> <td class="xl30" style="border-top: medium none; border-left: medium none;" x:num="5">5.00 </td> <td class="xl27">only sum for coloum have no</td> </tr> </tbody></table>

 

[Smitty]

Check out SUMIF in the helpfile.

It should do what you want.

HTH,

 

[Thiyagu]

i did try SUMIF function can't work ,,,or maybe my formula wrong..
I attach again a simple example..Their is just two column cell A2 & B2 , if two also have value then need to sum value at column cell C2 & D2 ---This i can do easly using sum function.
Only let say one column only had value A2 then sum only C2 value.

<table x:str="" style="border-collapse: collapse; width: 299pt;" border="0" cellpadding="0" cellspacing="0" width="397"><col style="width: 62pt;" width="82"> <col style="width: 71pt;" width="94"> <col style="width: 58pt;" width="77"> <col style="width: 54pt;" span="2" width="72"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 62pt;" height="18" width="82">Sam Score</td> <td class="xl24" style="width: 71pt;" width="94">Philips score</td> <td class="xl24" style="width: 58pt;" width="77">Sam Point</td> <td class="xl24" style="width: 54pt;" width="72">Philip Point</td> <td class="xl24" style="width: 54pt;" width="72">Sum</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" x:num="" height="18">0</td> <td class="xl24" x:num="">2</td> <td class="xl24" x:num="">25</td> <td class="xl24" x:num="">50</td> <td class="xl24" x:num="">50</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" x:num="" height="18">1</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">75</td> <td class="xl24" x:num="">50</td> <td class="xl24" x:num="">75</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" x:num="" height="18">1</td> <td class="xl24" x:num="">5</td> <td class="xl24" x:num="">75</td> <td class="xl24" x:num="">50</td> <td class="xl24" x:num="" x:fmla="=SUM(C4:D4)">125</td> </tr> </tbody></table>

 

[Smitty]

What formula are you using to derive the individual scores? It looks like 25 points for 0 & 50 for 1, but I don't see where row 3 follows.

See if this gets you close to what you want:

=IF(A2=0,D2,IF(B2=0,C2,SUM(C2:D2)))

 

[bosco_yip]

1] A1:G1, is your heading, A2:F2 are the datas

2] G2, formula copy down :

=SUMPRODUCT(ISNUMBER(A2:C2)*D2:F2)

Regards

 

[Thiyagu]

Really thanks a lot...the solution works well,,,

Really thanks a again