Google Spreadsheet Formula for cummulative sum

Question:


I have a google doc with a very simple form and I want it to calculate data live based on responses to the form.

My headings are:

DATE
AMOUNT
DESCRIPTION
TOTAL SPENT TO DATE
MONEY REMAINING

The form fills out the Date, amount, and description categories, but I would like the spreadsheet to calculate the Total Spent to Date based on a sum of the Amount column, and the Money Remaining Column should subtract the Total Spent to Date from a Given total.  Can you help me?

I know how to do this in excel, but I'm having a hard time with it recognizing the Form.  I tried using the Arrayforumula() and it does recognize the form, but I am unsure how to populate the formula correctly.

Thanks for any help you can give!


Solution:


Suppose you have following data in your spreadsheet:

Then put the following formula in Cell E1:
=ArrayFormula(IF(LEN(C:C);IF(ROW(C:C)=1;"TOTAL SPENT TO DATE";SUMIF(ROW(C:C);"<="&ROW(C:C);C:C));IFERROR(1/0)))

Then put the following formula in Cell F1:

=ArrayFormula(IF(LEN(C:C);IF(ROW(C:C)=1;"MONEY REMAINING";H4-E:E);IFERROR(1/0)))


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Google Spreadsheet advanced query formula

Question:

hiya, i'd like to be able to filter a list using logical OR on a series of terms i type into a cell. I guess i can do this with the SPLIT and ARRAYFORMULA functions, but how? Here is an example search with a single search term.

Would be great if someone could give me the correct arrayformula to search multiple terms. Feel free to copy & edit the doc.

Thanks

Alex

.......

Thanks, though this is not what i'm looking for (but i would use a query instead if the google sytnax allowed creation of new fields or complex where clauses, but it doesn't seem to)

I want to filter the list in sheet "datasheet" according to a series of search terms. So for example entering "red, green" into cell B1 of sheet "filtersheet" would return 5 items.

Thanks

Solution:


Suppose you have following data in 'datasheet':

Then put the following formula in Cell A3 of 'filtersheet':

=query(datasheet!A1:Z;"select * where B contains '" & join("' or B contains '";arrayformula(trim(split(B1;",")))) &"'")


Then you will get the following output on the 'filtersheet':

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Transpose data from a google form

Question:

Hi,

I am looking to transpose data from a google form.  It is a little tricky as the first few form columns are static but the last 12 will vary after each submission.

Example:

When the form gets submitted we have data in Columns A, B, C, D, E, F

I would like it to be transposed on Sheet 2 like this

A, B, C, D

A, B, C, E

A, B, C, F

Is this possible?

Thanks,

Jordan

Solution:


Suppose you have following data in Sheet1:

Then put the following formula in Cell A1 of Sheet2:

=ARRAYFORMULA(IF({{1},{1},{1},{0}};'Sheet1'!A1:C1;transpose('Sheet1'!D1:F1)))


Then you will get the following output on the Sheet2:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Google Spreadsheet Formula for calculation from cells between different sheets

Question:

( by _min_  )

Dear all,

I'm new on Docs/spreadsheets, so be patient.

We have an on-line spreadsheet for sales of several products (well described with their Code Article, Description, Quantity) and duplicated in two identical sheets, let say one for Adam and the latter for Eve.
Each guy can fill its sheet according with its own sales, so that Article 1002 can be 10 for Adam and 0 for Eve,  Article 2932 5 for Adam and 7 for Eve, and so on.
Once a month the boss want to have the report of sold articles AS SUM, does not matter who sold what.
And he wants to get a report CONTAINING ONLY sold products, regardless to the other thousands unsold.

Very simple, in principle.
In practice I do not understand how to do that.

Any idea?



Solution:

Have a look at the following the screenshot of the Sheet "Adam":

Have a look at the following the screenshot of the Sheet "Eve":

Now go to "Tools" menu >> "Script Gallery" >> search for "VMerge" and install it. You have to authorize the script for the first time.

Now in your sheet "Master", put the following formula in cell "A1":
=query(VMerge(Adam!A2:C;Eve!A2:C);"select Col1,sum(Col3) where Col3 >= 0 group by Col1 label Col1 'Code Article' , sum(Col3) 'Quantity' ")


Have a look at the following the screenshot of the Sheet "Master" having your desired results:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Google Spreadsheet sorting with multiple columns

Question:

( by Brett Walters )

Hi, 

I'm using google spreadsheet to scrape an HTML table and manipulate the data for re-publishing on the web.  Because our initial web form (which produces the HTML table) can't capture the info we need, I have staff manually adding data in a column,  All good up to here.  The new data is maiden name, and with the final output, I'd like it to sort maiden if it exists and then last name.  However, 'ORDER BY' treats the blank cell as a higher sorting cell.

This is what I've tried:

=QUERY(AddBirthName!$A$2:$D$2467;"select A,B,C where D = 1973 ORDER BY (B if B != ''),A";0)

this doesn't work...

Here is a example of the spreadsheet. Looking at the worksheet '1973' I'd like to see the order go 'Harding, Honday, Kimpinger, Korniger' ....

thanks!

Brett

Solution:

Here is the screenshot of the Sheet1:

Now after inserting the following formula:
=QUERY(Sheet1!$A$2:$D$2467;"select A,B,C where D = 1973 ORDER BY B,A";0)

Here is the screenshot of Sheet Sorting but not in the desired order. 

Now here is the formula to get the required output:

=QUERY(ArrayFormula(IF({{1},{1},{1},{1},{0}};Sheet1!$A$2:$D$2467;if(Sheet1!B2:B="";Sheet1!A2:A;Sheet1!B2:B)));"select Col1,Col2,Col3 where Col4 = 1973 ORDER BY Col5";0)

And here is the screenshot of Sheet sorted in the desired order:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Google Spreadsheet formatting the responses from forms

Question:

( by Catherine Humberger )

The results spreadsheet inserts my questions in columns and the responses in rows. I would like the questions in rows. How can I change this? Thanks in advance.

Solution:

Suppose if you are getting the responses from a form in a sheet named 'Sheet1', have a look at the screenshot of the example spreadsheet's 'Sheet1':

Now to change the format of the data, make a new sheet let's name it 'Sheet2'. In 'Sheet2' put the following formula in Cell A1:

=TRANSPOSE('Sheet1'!A:D)

The above formula will give you the output as the following:

So now you have the data organised as having responses in columns instead of rows, and you will be having questions in first Column instead of first row.

Note: In the formula I have used the sheet name as 'Sheet1' which you need to change according to your sheet name and also you need to change the range as per your requirement.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Google Spreadsheet Script to accept a maximum number of characters

Question:

I am trying to create a spreadsheet to be used in scheduling tweets, and I want to condition the cells to accept 120 characters or less so that I don't have to open Twitter/hootsuite to make sure they are the right length.  How can I do this? Either a warning or an error message would work. Thanks!

Solution:


Following is the script that will show you warning ( as comment ), that is an error message, in the current cell if you exceed the limit of 120 characters.

function onEdit(e)
{
  var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell();
  var activeCellValue = activeCell.getValue();
  var length = String(activeCellValue).length;
  
  if(length>120)
  {
    activeCell.setComment("Length is greater than 120 characters, Length is: " + length );
    Browser.msgBox("Length is greater than 120 characters, Length is: " + length );
  }
}

And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Google Spreadsheet Formula to calculate number of times a month occurs between two dates

Question:
 
( by contacts-fvc )

If I want to see how many times a certain month falls in between two dates (i.e.  count how many times November is in between the two columns [answer: 4]) how would I do that?

I have two columns like this:

Date Start Planned End

6/25/2012 9/11/2012

7/26/2012 2/7/2013

6/21/2012 10/11/2012

7/5/2012 12/19/2012

5/16/2012 1/2/2013

6/25/2012 12/11/2012

4/18/2012 10/3/2012

I've searched all excel and google spreadsheet forums and most of them can count them, but can't find a formula to get what I want.  I thought this formula would work: =ARRAYFORMULA(SUM((A2:A7>10/31/2012)*(B2:B7<12/1/2012))) but I don't know what I'm doing wrong. Please help! Thanks in advance

Solution:

Here is my formula to calculate the number of times November occurring between Column A and Column B:

=sum(ARRAYFORMULA(IFERROR(ROUNDDOWN(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12))/12);"")+iferror(if((IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);""))>0;if((month(A:A)+(IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);"")))>=11;1;0);"");"")+iferror((month(A:A)=11)*(month(B:B)=11);"")))

The formula will work as, if we have date having month 11 in Column A or in Column B then the formula will include it in counting that too.

So if we have database as:

DateStart PlannedEnd

11/15/2012 12/18/2012

then the above formula will give the result as 1.

So, now I have made formula to include the month November even if it is running (current) month.

And if you want to see the counting of month in each corresponding row,

then put the following formula in first cell of some new column:

=ARRAYFORMULA(IFERROR(ROUNDDOWN(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12))/12);"")+iferror(if((IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);""))>0;if((month(A:A)+(IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);"")))>=11;1;0);"");"")+iferror((month(A:A)=11)*(month(B:B)=11);""))

Following is the screenshot of my spreadsheet having some dates in Column A and Column B:

in above screenshot, you can see the Column C is having counts for each row. And Cell D8 is having the formula for total.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

Google Spreadsheet Query Group by

Question:
 
( by Johnny Wang88 )

Hi, Google docs Pros!!

I only know how to use sum in google docs, but would like to know how to use filter or search to sum the fields that I want. Here is what I need. I have a bunch of data like below:

as could be seen above, there will be different names and numbers for each name, and this datasheet will be growing each months with new data with more names and numbers, I want to sum the numbers of each specific names, for example like the result:

Thanks so much in advance....as this is probably very easy for Pros, but I've tried 2-3 hours still can't figure it out!! need help! Thanks!!

Johnny

Solution:

Suppose if you have the data in 'Sheet1' and then if you want to have the results in 'Sheet2' then look at the following example:

Have a look at the following screenshot of 'Sheet1':

If you want the results in same sheet then use the following formula:

=query(A:B;"select A,sum(B) where A <> '' group by A label sum(B) '' ")

OR you can also use the following formula:

=query(ArrayFormula(A:B);"select Col1,sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) '' ")

And if you want the results in new Sheet then, make a new sheet, lets call it 'Sheet2'

In 'Sheet2' put the following formula in Cell A1:

=query(Sheet1!A:B;"select A,sum(B) where A <> '' group by A label sum(B) '' ")

Alternatively you can also use the following formula:

=query(ArrayFormula(Sheet1!A:B);"select Col1,sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) '' ")

The above query will fill the data as shown in the screenshot below:

Have a look at the following screenshot of 'Sheet2':

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive