Google Spreadsheet If and Search functions with Arrayformula

Question:

( by Violet )

I'm new to Google Docs and have recently created a Google Form with responses contained in a Google Sheet.  I want the sheet to calculate values but the cells only contain text and this has to remain for the end user.  Is is possible to calculate the following in cell W2:

IF H2 contains STANDARD then 520, IF I2 contains UPGRADE then 590, IF K2 contains UPGRADE then 640

Any help greatly appreciated. 

=====================================

Me:

Hi Violet,

try the following formula in Cell W2:

=if(H2="STANDARD";520;if(H2="UPGRADE";590;if(H2="UPGRADE";640;)))

If you provide more details then we can help you more, like you can use Arrayformula to compute this for whole column W with a single formula...

And what if both Cells H2 contains "STANDARD" and I2 contains "UPGRADE" then ??

It will help if you share your Google spreadsheet (not a screenshot, not an Excel file) with say 5 or more rows of sample but realistic data, and 

    a) tell us what you need help with

    b) in which cell?

        of which sheet?

    c) show us your expected result

        along with needed logic/explanation

I hope this helps you,

===============================

Violet:

H

i Kishan

Thanks for your prompt reply.  I have tried the formula you provided but if does not work - I probably didn't explain what I required correctly!

To answer your query cells H2 and I2 can not both contain data as the spreadsheet is based on a form with multiple choice answers but 

I have re looked at the sheet and can now query a single column:

If cell G2 (G3, G4 etc) contains:

Standard then 520

Option 1 then 590

Option 2 then 640

Option 3 then 685

Option 4 then 700

Option 5 then 750   

I have input some data and have a spreadsheet to share.  Do I just share this through Google Drive or send as an attachment?

Many thanks

=================

Violet:

This sheet records responses to a form for customer carpet selection. I want column Y to calculate the total due from the customer to enable us to match to an invoice at a glance. I found an iferror formula on the forum and adpated it but it only provide for one argument

Solution:

Have a look at the screenshot of the spreadsheet:

Now put the following formula in Cell W1:

=arrayformula(if(row(G:G)=1;"Total due from purchaser";if(G:G="";"";(iferror(if(search("standard";G:G);520);iferror(if(search("option 1";G:G);590);iferror(if(search("option 2";G:G);640);iferror(if(search("option 3";G:G);685);iferror(if(search("option 4";G:G);700);iferror(if(search("option 5";G:G);750);)))))))+(if(Q:Q="YES";250;0)))))


Now if Column G contains "standard" then Column W will add as 520 to its value and it Column Q has "Yes" then Column W will add 250 more to its value and will have 770 (520+250).

Similarly if Column G contains "option 1" then Column W will add as 590 to its value and it Column Q has "Yes" then Column W will add 250 more to its value and will have 840 (590+250).

And so on...

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 and 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 to count in different sheets

Question:

( by tomtom328 )

Dear all, 

I like to create a formula for inventory count:

Column O (of many sheets) outlines the different types of inventory that i carry:   itemA, itemB, itemC, itemD, itemE

I need a formula that simply counts through all the sheets the different types of items that are being used:  

for ITEM.A count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemA")

for ITEM.B count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemB")

for ITEM.C count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemC")

for ITEM.D count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemD")

for ITEM.E count: =countIf('sheet1','sheet2','sheet3' !O:O,"itemE")

now, some of these items that I carry are in the inventory, but are not being used; how do i count the number of items that are not in use  & at the same time, (in the above formula) account or subtract out the ones not being used.

in column F, I would mark down the comments: NOTINUSE

How do i do count that shows the number of itemsA,B,C, D, E   not in use?

for ITEMA NOTINUSE COUNT =   ?

Thank you greatly in advance!

Solution:

Following are the screenshot of sheet1, sheet2, sheet3 containing data as mentioned in the question:

sheet1:

sheet2: 

sheet3:

Now to count the no. of "itemA" in sheet1, put the following formula in Cell B1 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!O:O="itemA")))


To count the no. of "itemA" that is "NOTINUSE" in sheet1, put the following formula in Cell B2 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F="NOTINUSE";'sheet1'!O:O="itemA")))


To count the no. of "itemA" that is in use in sheet1, you can have it by B1-B2, or you can also use the following formula in Cell B3 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F<>"NOTINUSE";'sheet1'!O:O="itemA")))


Have a look at the screenshot of "sheet4":

Now to count the no. of "itemA" in all sheets, put the following formula in Cell B5 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!O:O="itemA")))+counta(iferror(filter('sheet2'!O:O;'sheet2'!O:O="itemA")))+counta(iferror(filter('sheet3'!O:O;'sheet3'!O:O="itemA")))

To count the no. of "itemA" that is "NOTINUSE" in all sheets, put the following formula in Cell B6 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F="NOTINUSE";'sheet1'!O:O="itemA")))+counta(iferror(filter('sheet2'!O:O;'sheet2'!F:F="NOTINUSE";'sheet2'!O:O="itemA")))+counta(iferror(filter('sheet3'!O:O;'sheet3'!F:F="NOTINUSE";'sheet3'!O:O="itemA")))


To count the no. of "itemA" that is in use in all sheets, you can have it by B5-B6, or you can also use the following formula in Cell B7 of sheet4:

=counta(iferror(filter('sheet1'!O:O;'sheet1'!F:F<>"NOTINUSE";'sheet1'!O:O="itemA")))+counta(iferror(filter('sheet2'!O:O;'sheet2'!F:F<>"NOTINUSE";'sheet2'!O:O="itemA")))+counta(iferror(filter('sheet3'!O:O;'sheet3'!F:F<>"NOTINUSE";'sheet3'!O:O="itemA")))

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 and 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 ImportHtml auto update or refresh

Question:

I'm using the =ImportHtml function to get some data from an HTML table.

It works okay, but I need it to refresh. The HTML data will change periodically, so I need the spreadsheet to periodically check to see if the data have been updated.

Right now it seems to be pretty static, and I can't find a setting or anything to make it check for new data.

I've searched through some forums and can't find an answer or a workaround.

Please help!

(I'm a programmer, so feel free to get as techy as needed. And if anyone knows of anything in the Java API that can help me, please advise)

Solution:

Following is the work around to get the auto-refreshed values (results) from ImportHtml fomula:

You have to pass the parameters at the end of the URL (these parameters are meaning less, adding these parameters to URL will not change your results, and these parameters are used to only change the URL again and again so that we can get the refreshed values)

For example:

If you are having the following URL in ImportHtml formula:

=ImportHtml("http://www.nasdaq.com","table",4)

Then change it to:

=ImportHtml("http://www.nasdaq.com/?"& minute(now()),"table",4)

The above formula will update your imported content from html every minute.

But in this case also chances are that you will get the same table again after an hour, this is because suppose minute(now()) returns 6 as value, so it is going to return 6 after an hour hour, so chances are you will get 60 times updated value and then again you will get same results.

So to avoid it you can try following formula:

=ImportHtml("http://www.nasdaq.com/?"& year(now()) & month(now()) & day(now()) & hour(now()) & minute(now()),"table",4)

The above formula will give you updated results every minute.

And if you want updated result every second then change it to:

=ImportHtml("http://www.nasdaq.com/?"& year(now()) & month(now()) & day(now()) & hour(now()) & minute(now()) & second(now()),"table",4)

Note:

If you don't see auto update then just press "delete" key on any empty cell this will make your spreadsheet refresh this formula and it will work. (And if you want to auto update (refresh) then you can code a script for that)

I hope the above solution helps you.

How to write a script in Google Spreadsheet

If you are new to Google Spreadsheets and you have never written a script before or if you don't know anything about it, then this tutorial will help you to know how you can write a script in a Google Spreadsheet.

Step 1) Open Script Editor:
From "Tools" menu, click on "Script editor..."

Look at the following screenshot:

This will navigate to you to the Script Editor.
Have a look at the screenshot below:

So, now you are ready to write the code in your Google Spreadsheet script editor. You may notice some code already exists on the script, you may delete it and start fresh with your own script.

Now if you want to print "Hello", then make a function in your script with whatever the name you like. I have made the function functionHello(), have a look at the following script:

function functionHello()
{
  Browser.msgBox("Hello");

}

Now, copy the above script in your script editor and try to run the function:

And then go to your spreadsheet. You will see the small pop-up window displaying message "Hello":

Hope this helps you to understand how to open script editor and start writing scripts in Google Spreadsheet.

Send a Google Spreadsheet sheet to an email address daily by setting a trigger

Send a Google Spreadsheet sheet to an email address weekly or daily or hourly or minutely by setting a timer trigger.

You must have been wondering about whether "Is there a way to send a Google Spreadsheet sheet to an email address daily and that too by a script" If yes then how?

Then you would be very happy if you will have a look at the following script which will help you in achieving it.

So, here is the script:

//////////////

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Send Email", functionName: "sendEmail"}];
  ss.addMenu("Scripts", menuEntries);
};

function sendEmail() {

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  //var email = Session.getUser().getEmail();
  var email = Session.getEffectiveUser();

  var subject = "this is my subject";

  var body = "this is my body :)";

  

  var oauthConfig = UrlFetchApp.addOAuthService("google");

  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");

  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");

  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");

  oauthConfig.setConsumerKey("anonymous");

  oauthConfig.setConsumerSecret("anonymous");

  

  var requestData = {"method": "GET", "oAuthServiceName": "google", "oAuthUseToken": "always"};

  var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="

      + ssID + "&gid=0&portrait=true" +"&exportFormat=xls";

    

  var result = UrlFetchApp.fetch(url , requestData);  

  var contents = result.getContent();

  MailApp.sendEmail(email,subject ,body, {attachments:[{fileName:sheetName+".xls", content:contents, mimeType:"application//xls"}]});

};

///////////////

You have to copy paste this script in the "script editor", which you will find at the menu bar of the spreadsheet.

I have also coded the "open function" such that after you execute this script and then reopen your spreadsheet you will view "Scripts" menu at the menu bar. Have a look at the screenshot below. 

In this "Scripts" you will have the entry "Send Email", clicking on it will send you the copy of that spreadsheet.

And now to set the trigger you have to go to "Script editor" and there you have to click on the trigger icon below the "Publish" tool bar command. Have a look at the screenshot below:

Then click on "Add a new trigger", then in Run select "sendEmail" and in Events click on "Time-driven" and then select at whatever frequency you want to execute this trigger.

I hope this script would have solved your problems of manually emailing the same spreadsheet again and again.

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 and 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 calculate SUM values according to a criteria

Let's see a example of it:

I'm trying to have my spreadsheet that calculates SUM values according to the person's name selected in a drop down list.  I have names in a drop down list in column A. With days totals in column B and Month totals in column C. when an person makes a entry, they select their name and add the day and month amounts. 

I need to have each person have their days totals SUM up in one cell and month totals Sum up in another cell.

So in the above table we want to get the values in the range F3:G6

So by putting following formulas at the mentioned cells in the spreadsheet we will get the results:

F3 =sumproduct(filter(B2:B,A2:A=E3))

F4 =sumproduct(filter(B3:B,A3:A=E4))

F5 =sumproduct(filter(B4:B,A4:A=E5))

G3 =sumproduct(filter(C2:C,A2:A=E3))

G4 =sumproduct(filter(C3:C,A3:A=E4))

G5 =sumproduct(filter(C3:C,A3:A=E5))

By inserting these formulas we will get the results as shown below in the screenshot:

I hope this makes you understand, the functioning of "sumproduct" and "filter" funtions.

Bug found in Google SpreadSheet when you have more than 101 rows

I tested this issue repeatedly by deleting and adding rows and then selecting whole data by pressing "ctrl + A", and what I found is:

If you are having row up to 101 then its not a issue at all.

But when you are having rows more than 101 but not more than 150 and if you select all then actually it will select all but it will show as if you have selected only up to row 101, and display as if rows below 101 are not selected. Even if you then just select range B102 to D121, it will not be highlighted as this range has been selected, but if you copy it (ctrl + c) and then go to some other cell and paste it you will notice that it has copied the data and it is functioning properly.

And if you are having rows more than 151 but not more than 200 and if you try to select all (ctrl + A), then it will show as if you have selected only upto row 151, but not below it. And this goes on in the multiples of 50.

Actually while selecting these cells, they are not displayed as selected (not highlighted) but they are selected.

Note: if you have 233 rows (even if you have data only in the first row, may be only in one Cell A1, it will consider last row no. 233), and if you select all by "ctrl A" it will select up to rows 201 only and not below it.

This blog has been posted at 4:30 A.M. (IST) as on 19th August 2012. May be when you read this blog GOOGLE might have recityfied it.

I hope this bug will be fixed soon by GOOGLE.

How to count how many times a name appears on one sheet

If you are wondering how to count a specific value (may be a number or a name or a date or any content of a cell) from a specific range of one sheet to another sheet of a spreadsheet then here is the formula.

Suppose you have a Sheet named "Summary" and you want to count how many times "John" appears in a range A4:S34 in the Sheet named "SignIn" (which is in the same spreasheet), then you can count it with the help of function "COUNTIF". Here is the formula:

=COUNTIF(SignIn!A4:S34,"John")

Here, the above formula will return the number of times "John" appearing in Sheet named "SignIn".

Google Spreadsheet - How to quickly scroll down to desired row - Option 1

If you are having a huge amount of rows in your spreadsheet, then you must be wondering how to navigate to your desired row immediately, there are various ways to achieve it. You can scroll if down with the help of a mouse, but take lots of time and you can press "Page Down" key, but sometimes if there are rows in thousands then it will also take your time to navigate.

Following is the script which will help you to navigate to your desired "Cell":

function onOpen()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name:"MyFunction", functionName:"myFunction"}];
  sheet.addMenu("Scripts", entries);
};

function myFunction()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();

  var input = Browser.inputBox("Input", "Input Row No. To Navigate:", Browser.Buttons.OK);
  mysheet.setActiveCell(mysheet.getDataRange().offset(input-1, 0, 1, 1));
};

As soon as you input the row number, you will be navigated to the "Cell" which is at the first Column in that row.

And in "onOpen()" function, I have coded to insert "myFunction" in the "Scripts" menu at the menu bar. I have entered this function in the script so that you don't have to run the script from script editor again and again and so that you can do it directly from the menu bar.

So now you can navigate or scroll to the desired row.

Here is the option to directly navigate to the last row by default:
http://igoogledrive.blogspot.com/2012/08/scroll-down-to-last-row.html

You can also navigate to your last modified cell, here is the script for it:
http://igoogledrive.blogspot.com/2012/08/how-to-get-location-of-last-modified.html 

Formula to check a criteria in a specific range.

Suppose you want to get "FAIL" in a cell if a certain criteria is met in another range of cells. So in the table below, J4 would return the text FAIL, and J5 would return the text PASS.

Now, paste this formula in cell J4

=if(COUNTIF(C4:I4,"X")=0,"PASS","FAIL") 

And this formula in cell J5

=if(COUNTIF(C5:I5,"X")=0,"PASS","FAIL") 

Here function "COUNTIF" will search for the criteria "X" and if this criteria returns 0, it means student has passed all areas and J5 will displays "PASS".

I hope the above example will make you understand how to use the formula "CountIf" in Google Spreadsheets.

Importing a Range of one spreadsheet to another spreadsheet

If you want to refer a cell to a Spreadsheet from different Spreadsheet, then here is the formula which will do your job:

=ImportRange("0AmMTqpzD9YRndEZTTV9XNWpEaGM1RHJqY0ZIY3RTVnc","Sheet1!A1:A1")

ImportRange is a function which is extremely helpful when you have to call in the data from other spreadsheet. It is used to synchronize the master spreadsheet and the spreadsheets from which data is fetched.

Note:

Here "0AmMTqpzD9YRndEZTTV9XNWpEaGM1RHJqY0ZIY3RTVnc" is the key of the Spreadheet, which you can find in the URL.


And if you want to import range "A1:C3" of Sheet "my Sheet" from one Spreadsheet to another one then here is the formula for that:

=ImportRange("0AmMTqpzD9YRndEZTTV9XNWpEaGM1RHJqY0ZIY3RTVnc","my Sheet!A1:C3")

I hope this post helps you to understand the basics concept of ImportRange function.

Google spreadsheet normal view disappears and only grey screen appears

While opening a spreadsheet, you may notice that, it opens normally but only for a second, then the page goes blank, "grey screen appears", leaving the title and tabs on top and the row numbers along the side, but nothing else. Following is the screenshot:

It seems there is a some problem with Google Docs.
But here is a solution, you can see the spreadsheet by following these steps

1) Go to the View menu.
2) Then, point your mouse to Freeze columns....
3) Select Freeze 3 columns option.

After performing these steps, you will be able to view and edit data.

Notice: if you freeze 1 or 2 columns - only these columns will be seen. Full spreadsheet becomes visible with 3 columns frozen.

I hope the above solution will help you to open your spreadsheet normally, and I hope GOOGLE DOCS will figure out this issue soon.