yogi_Use SUMPRODUCT On Data In Another Sheet And Its Equivalent For Data Filtered In The Same Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
ulviyelen said:
Hi ! I loved google doc and I wanted to use it for bill tracing for my work. I made a form that feeds a spreadsheets. Eveything is ok until I wanted to retrive some spesific data. Here is what I wanted to do:
On the different cell I want to have specific formula:
C D E
1) Amount Date Day(until pay)
2) 510 15-11-2011 30
3) 240 15-12-2011 60
.. ... ........ ...
for this two rows the formula is =ArrayFormula(SUMPRODUCT(C2:C3;E2:E3)/(SUBTOTAL(109;C2:C3))) > this works fine..
The problems are;
* I feed the sheet with a form. I mean adding always new data.
So if I write this to make it more expanded;
=ArrayFormula(SUMPRODUCT(C2:C10000;E2:E10000)/(SUBTOTAL(109;C2:C10000)))
subtotal works but sumproduct doesn't work. The empty cells also calculated and gives wrong results.
* If I filter results (for example only phone bills) the filtered result isn't correct.
I wish I hade sumproduct function_code for subtotal.
------------------------------
here are couple of solutions to the problem