Saturday, October 29, 2011

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                  
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:
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;
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