Hey guys,
I am trying to gather some data using the sumproduct formula. But I am not sure, if it's the best way because it is not working properly.
C1 is a Month but in textform (e.g. August) on the sumup sheet, in which the formula is.
For the formula given above, it is supposed to count/sum up all values from the certain month (in this example August) but until now it either gives "0" as output or wrong values. This is some sample data, the formula is accessing. For August the output for W/M should be 20 but it shows 0. It works however for the number of entries and the number of shipments.
Unfortunately the same formula also gives me "0" for the other values, when I am accessing a different sheet.
Any ideas on that?
Maybe use a different formula?
Jan
I am trying to gather some data using the sumproduct formula. But I am not sure, if it's the best way because it is not working properly.
Code:
=IF(C1="","",SUMPRODUCT((MONTH('Data In Chinese'!$A$2:A50000)=VLOOKUP($C$1,Calendar!$A$2:$C$13,3)*('Data In Chinese'!E2:E50000))))For the formula given above, it is supposed to count/sum up all values from the certain month (in this example August) but until now it either gives "0" as output or wrong values. This is some sample data, the formula is accessing. For August the output for W/M should be 20 but it shows 0. It works however for the number of entries and the number of shipments.
Code:
<table width="352" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2432; width:57pt" span="2" width="76"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:2112;width:50pt" width="66"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:1568;width:37pt" width="49"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:1440;width:34pt" width="45"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:1280;width:30pt" width="40"> </colgroup><tbody><tr style="mso-height-source:userset;height:30.0pt" height="40"><td class="xl66" style="height:30.0pt;width:57pt" height="40" width="76">Date</td><td class="xl66" style="width:57pt" width="76">Number of Entries</td><td class="xl66" style="width:50pt" width="66">Number of Items</td><td class="xl66" style="width:37pt" width="49">CBM</td><td class="xl67" style="width:34pt" width="45">W/M</td><td class="xl67" style="width:30pt" width="40">shpt</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt" height="20" align="right">02/08/2011</td><td class="xl69" align="right">1</td><td class="xl69" align="right">20</td><td class="xl69" align="right">0.5</td><td class="xl69" align="right">10</td><td class="xl69" align="right">1</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt" height="20" align="right">03/08/2011</td><td class="xl69" align="right">2</td><td class="xl69" align="right">30</td><td class="xl69" align="right">1</td><td class="xl69" align="right">10</td><td class="xl69" align="right">2</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt" height="20" align="right">04/09/2011</td><td class="xl69" align="right">3</td><td class="xl69" align="right">10</td><td class="xl69" align="right">0.7</td><td class="xl69" align="right">10</td><td class="xl69" align="right">1</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt" height="20" align="right">05/09/2011</td><td class="xl69" align="right">4</td><td class="xl69" align="right">60</td><td class="xl69" align="right">0.2</td><td class="xl69" align="right">10</td><td class="xl69" align="right">2</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt" height="20" align="right">06/09/2011</td><td class="xl69" align="right">3</td><td class="xl69" align="right">20</td><td class="xl69" align="right">0.5</td><td class="xl69" align="right">10</td><td class="xl69" align="right">1</td></tr><tr style="height:15.0pt" height="20"><td class="xl68" style="height:15.0pt" height="20" align="right">07/10/2011</td><td class="xl69" align="right">2</td><td class="xl69" align="right">30</td><td class="xl69" align="right">1</td><td class="xl69" align="right">10</td><td class="xl69" align="right">2
</td></tr> </tbody></table>Unfortunately the same formula also gives me "0" for the other values, when I am accessing a different sheet.
Code:
<table width="807" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:640;width:15pt" width="20"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:12800;width:300pt" width="400"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:4128;width:97pt" width="129"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:2688;width:63pt" width="84"> </colgroup><colgroup><col style="width:54pt" width="72"> </colgroup><colgroup><col style="mso-width-source:userset;mso-width-alt:3264;width:77pt" width="102"> </colgroup><tbody><tr style="height:14.25pt" height="19"><td class="xl71" style="height:14.25pt;width:15pt" height="19" width="20">
</td><td class="xl66" style="width:300pt" width="400">Item</td><td class="xl73" style="width:97pt" width="129"> USD </td><td class="xl66" style="width:63pt" width="84">Unit</td><td class="xl66" style="width:54pt" width="72"># of units</td><td class="xl73" style="width:77pt" width="102"> USD Total </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">1</td><td class="xl67">Warehouse in handling</td><td class="xl72"> $ 2.34 </td><td class="xl67">per w/m</td><td class="xl67" align="right">0</td><td class="xl74"> $ - </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">2</td><td class="xl67">Entry agent fee</td><td class="xl72"> $ 23.44 </td><td class="xl67">per entry</td><td class="xl67" align="right">13</td><td class="xl74"> $ 304.69 </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">3</td><td class="xl67">Customs clearance</td><td class="xl72"> $ 31.25 </td><td class="xl67">per entry</td><td class="xl67" align="right">13</td><td class="xl74"> $ 406.25 </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">4</td><td class="xl67">Bond in declaration</td><td class="xl72"> $ 23.44 </td><td class="xl67">per entry</td><td class="xl67" align="right">13</td><td class="xl74"> $ 304.69 </td></tr><tr style="mso-height-source:userset;height:25.35pt" height="33"><td class="xl70" style="height:25.35pt" height="33">5</td><td class="xl69" style="width:300pt" width="400">Handling fees on amendment or cancellation of the declaration sheet</td><td class="xl72"> $ 23.44 </td><td class="xl67">per entry</td><td class="xl67" align="right">13</td><td class="xl74"> $ 304.69 </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">6</td><td class="xl67">Documentation</td><td class="xl72"> $ 23.44 </td><td class="xl67">per shpt</td><td class="xl67" align="right">11</td><td class="xl74"> $ 257.81 </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">7</td><td class="xl67">Handling agent fee</td><td class="xl72"> $ 15.63 </td><td class="xl67">per shpt</td><td class="xl67" align="right">11</td><td class="xl74"> $ 171.88 </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">
</td><td class="xl67">
</td><td class="xl72">
</td><td class="xl67">
</td><td class="xl67">
</td><td class="xl74">
</td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">
</td><td class="xl68">Overseas suppliers</td><td class="xl72">
</td><td class="xl67">
</td><td class="xl67">
</td><td class="xl74">
</td></tr><tr style="height:14.25pt" height="19"><td class="xl71" style="height:14.25pt" height="19">
</td><td class="xl66">Item</td><td class="xl73"> USD </td><td class="xl66">Unit</td><td class="xl66">
</td><td class="xl74">
</td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">1</td><td class="xl67">Warehouse in handling</td><td class="xl72"> $ 2.34 </td><td class="xl67">per w/m</td><td class="xl67" align="right">0</td><td class="xl74"> $ - </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">2</td><td class="xl67">Entry agent fee</td><td class="xl72"> $ 23.44 </td><td class="xl67">per entry</td><td class="xl67" align="right">0</td><td class="xl74"> $ - </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">3</td><td class="xl67">Customs clearance</td><td class="xl72"> $ 31.25 </td><td class="xl67">per entry</td><td class="xl67" align="right">0</td><td class="xl74"> $ - </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">4</td><td class="xl67">Bond in declaration</td><td class="xl72"> $ 23.44 </td><td class="xl67">per entry</td><td class="xl67" align="right">0</td><td class="xl74"> $ - </td></tr><tr style="mso-height-source:userset;height:25.35pt" height="33"><td class="xl70" style="height:25.35pt" height="33">5</td><td class="xl69" style="width:300pt" width="400">Handling fees on amendment or cancellation of the declaration sheet</td><td class="xl72"> $ 23.44 </td><td class="xl67">per entry</td><td class="xl67" align="right">0</td><td class="xl74"> $ - </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">6</td><td class="xl67">Documentation</td><td class="xl72"> $ 23.44 </td><td class="xl67">per shpt</td><td class="xl67" align="right">34</td><td class="xl74"> $ 796.88 </td></tr><tr style="height:14.25pt" height="19"><td class="xl70" style="height:14.25pt" height="19">7</td><td class="xl67">Handling agent fee</td><td class="xl72"> $ 15.63 </td><td class="xl67">per shpt</td><td class="xl67" align="right">34</td><td class="xl74"> $ 531.25 </td></tr> </tbody></table>Any ideas on that?
Maybe use a different formula?
Jan
