Monday, April 1, 2013

yogi_Compute Row By Row Running Balance By Name And Month

                                          Google Spreadsheet   Post  #1092
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Apr 1, 2013
user JayPee Alpano :(!searchin/docs/JayPee/docs/2BAX4a29xrg/sXL5lGHuefIJ)
Hi Everyone,

I badly need help as this is the last piece I need to complete my project.

I have a spreadsheet which is filled out by from a Google Form.
The data is entered by multiple people.
They select the Month, and their Name from a List, making sure that they enter it exactly the same everytime.

The sample sheet linked to the form is found here:

I need to have a RUNNING BALANCE (Month-to-Date), FOR EACH PERSON, FOR EACH MONTH and put it in Column H.

Timestamp         Person      Month Day Sales MTD Sales
3/26/2013 22:51:34 Cain, Mark     March 25 3
3/27/2013 0:38:30 Cain, Mark     March 26 12
3/27/2013 2:28:45 Santoy, Ray   March 25 12
3/27/2013 5:26:54 Santoy, Ray   March 26 12
3/27/2013 6:08:10 Cain, Mark      April 1 10

So what I need is a formula in col F which will:
1. Check the Name of the person (col B)
2. Check the Month (col C)
3. Check all Days which is less than or equal (<=) to the Day (col D)
4. Sum all Sales (Col E) that matches all the criteria in items 1-3 above.

So basically, I expect that
F2 = 3
F3 = 15
F4 = 12
F5 = 24
F6 = 10

Formula works if I manually enter it per row.  But I need it to automatically copy down for every new submission through the form.
I am pulling my hair out.

I hope Yogi will see this for he has been the savior in most posts I have seen so far.
following is a solution to the problem