Tuesday, September 10, 2013

yogi_Compute SUM COUNT And AVERAGE Of Amount For Specified Item (or all Items)

                                          Google Spreadsheet   Post  #1366
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013
user Steve Nordstrom (http://productforums.google.com/forum/?zx=ktl89mk9ta16#!mydiscussions/docs/oLtTAejtd0s)

Example from a budget spreadsheet - I would like to filter a single recurring item, see the total spent on this item, and calculate the average spent on this item for a week/month/quarter/year, etc, and calculate the percent this item represents to a total spent for a week/month/quarter/year.
Apparently, Sheets cannot do calculations on filtered subsets of a column of data.
---
Well, it seems that I cannot drag a Sheets file from Google Drive to this attach window.

Sample base document
ItemAmount
Food$55.00
Gas$25.00
Utilities$100.00
Food$47.00
Gas$15.00
Utilities$95.00
Food$63.00
Gas$31.00
Utilities$87.00
Desired Filter features
Food$55.00
Food$47.00
Food$63.00
Filter items total$165.00
Filter items listed3
Filter items average$55.00

There!  That worked.
All I want (which I can get in Excel and Open Office) is some statistical data from a filtered column.
In the example above I want some info on each of the budget items listed.
So I filter for each one consecutively.
I am showing "Food" as the filtered item in the example above.
I want to see the total for all the entries in the filtered data, the number of entries found, and the average amount of the items based on the number of entries listed. 
Simple.
Except if I set these formulas up at the bottom of my budget spreadsheet, when I apply a filter for a particular item, the formula cells do not see the filtered items, they still see all the entries in the entire column. (even though I cannot see them at this point)
I hope this helps.
Maybe I am missing something that is already there.
Educate me.
Thanks.
-----------------------------------------------------------------------------------------------------------------------------------------------