yogi_Compute Remaining Inventory Considering Items Sold As Delineated In Sheets A and B

Google Spreadsheet   Post  #1975

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-26-2015

post by  Wardssmith:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/vh18-76CkEI

Is there a way to get a sum of all A2 cells in all sheets using a wildcard of sorts?
Is there a way to get a sum of all A2 cells in all sheets using a wildcard of sorts? 

May be more involved than originally suggested...In sheet 2 (Inventory) I would like to keep a running total of sold units based on the reports of newly generated sheets A  and B.

https://docs.google.com/spreadsheets/d/16VSQpS9dTJMoXXU6TIQErc8z91N1vRLDMlJGAAJnrRo/edit?usp=sharing
-----------------------------------------------------------------------------------------------------

yogi_Pull Data From Table In Columns A To E Where Entries In Column D Have A Matching Entry In Column G

Google Spreadsheet   Post  #1974

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-22-2015

post by  johnsmith84563:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/LiugP7E_8p8

How to copy data from a range where one column has to match entries from another column?

Here is an example spreadsheet that I have made to illustrate my point:

https://docs.google.com/spreadsheets/d/13PY8DxsxswKdHgAxA1CebAvZgErZifb_hekYVvMrslI/edit?usp=sharing

I have five columns of data. I would like use a query function (or whatever works best) to select the data where column E (water) = yes  AND where column D (fruit) = one of the entries in column G

For example, I want to create a list of those people who have "yes" in the water column and if their fruit matches one of the fruit entered in column G. I know that in my example spreadsheet I could just use several "or d=fruit" clauses. However, in my actual spreadsheet, there are a couple hundred universities in the data range, and I need to select individuals from a list of about twenty universities. I would prefer to not have twenty clauses in the formula!

So the output in the test spreadsheet that I am looking for would look like that which is displayed in F25:H31

Thank you for your help!

---------------------------------------------------------------------------------------------------------------------------

3 posts by 3 authors

 

 

yogi_ Compute Running Balance For Row By Row Deposits And WithDrawlas And A Given Starting Balance

Google Spreadsheet   Post  #1973

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-19-2015

post by  Ilene Kahn:
https://productforums.google.com/forum/#!mydiscussions/docs/NxLwlPB2Cv4
Checkbook Register in sheets - balance auto calculation needed
When I add lines to checkbook register template in sheets, formula for balance no longer works. How do I get new lines to automatically calculate with formula from above lines?
---
here is link to my check register sheet:

https://docs.google.com/spreadsheets/d/1bk2Bfefz0pxcW77YtKriyx2nfrp1SaDhYjg1ATDTu1o/edit#gid=0

note in cell H83 (or is it 83H, I created my own one time formula

---------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Get Latest Value For Every ID By Latest Date

Google Spreadsheet   Post  #1972

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-19-2015

post by  Christopher Krier:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/EhetRDTQwR0
Getting latest value for specified ID by date stamps
I am trying to figure out a way to filter out data from a log to grab the latest value by ID with the latest date.
Trying various methods, I got close but not quite perfect solutions that were used on other answers.

I am also looking for a generic answer, not an exact one designed per sheet. I am trying to find the logic behind this solution.

Basic Example

Column Data
A = Date Stamps
B = ID
C = Value

Example but not valid syntax. (For logic Purpose)
Using a QUERY(A:C, "select B,C where A=(MAX(A))") would result in the latest date being used showing all ID's and Values.
However the flaw in that (If that was valid syntax) would be that if there is some values that don't share the latest date but are slightly older. They will be filtered out completely.

What is wanted,I am looking for retrieving all Values for each ID of the ID's latest date stamp.

If possible, I would like an example using both QUERY and other functions, unless it is not possible with QUERY.
If possible, I would like it to be possible to include other columns of data but matching the same rules.

Rules1. ID's are only shown once with their value.
2. Return value reflects the latest entry of specified ID.

Latest being defined as that entries time stamp column being the latest date.

Complete ExampleGoogle Sheet
-----------------------------------------------------------------------------------------------

yogi_Array Formulas For Row By Row Cumulative Results For Columns E F G H And I

Google Spreadsheet   Post  #1971

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-13-2015

post by  Liuba:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ufdJB78Vc1I 
How to convert a SUM(FILTER() into an ARRAYFORMULA
Dear Guys,

I´ve tried to use some solutions of similar problems discussed here, but honestly, no success. So, I´ve finally decided to ask for your help.

I´ve got this table below registering payments of clients. Every client has an "account", so a client can pay more than charged and the extra money is added to his or her account.  A client can also pay less than charged and pay off later.

Columns E, F, G show the sum of what is charged, paid, and available on the account of a client from the beginning of the table up to the current row.

You track step-by-step changes of accounts.

Columns H, I, J show similar sums, but from the beginning of the table to the very end.

Whichever row you look at, you see the actual status of accounts.

Link to the table: https://docs.google.com/spreadsheets/d/11kgdW79rSOcCrBeTDGy5rSVkEOiBwsINnQ2ehDMJfJo/edit?usp=sharing

At the moment I use SUM(FILTER) formulas in red headed columns (E, F, H, I):

Column E is the sum of what has been charged to a client up to this row:

=SUM(FILTER($B$2:$B2,$D$2:$D2=$D2))

=SUM(FILTER($B$2:$B3,$D$2:$D3=$D3))

=SUM(FILTER($B$2:$B4,$D$2:$D4=$D4))

Column F is the sum of what a client has paid up to this row:

=SUM(FILTER($C$2:$C2,$D$2:$D2=$D2))

=SUM(FILTER($C$2:$C3,$D$2:$D3=$D3))

=SUM(FILTER($C$2:$C4,$D$2:$D4=$D4))

Column H is the sum of what has been charged to a client from the beginning and even after this row:

=SUM(FILTER($B:$B,$D:$D=$D2))

=SUM(FILTER($B:$B,$D:$D=$D3))

=SUM(FILTER($B:$B,$D:$D=$D4))

Column I is the sum of what a client has paid from the beginning and even after this row:

=SUM(FILTER($C:$C,$D:$D=$D2))

=SUM(FILTER($C:$C,$D:$D=$D3))

=SUM(FILTER($C:$C,$D:$D=$D4))

Columns G and J were easy to convert into ARRAYFORMULA and show the difference between what was paid and what was changed.

From other tutorials I understood I cannot use SUM formulas in ARRAYFORMULA. Well, stubbornly, I´ve tried and the result is either "0" down the whole column or the sum of the whole column in each cell...

I would greatly appreciate if you explain me what I should do to make an ARRAYFORMULA for columns E, F, H, and I, taking into account that the list of clients is constantly growing, so every time we sum up data, we need to refer to the name of a client in this particular row.

Thanks in advance!

Sincerely,

Liuba

------------------------------------------------------------------------------------------------------------------------

yogi_Array Formulas For Row By Row Cumulative Results For Columns E F G H And I