## Tuesday, September 10, 2013

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 10, 2013

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 Item Amount 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 listed 3 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.
-----------------------------------------------------------------------------------------------------------------------------------------------