Hot answers tagged

10

=SUMIF(D:D,"bob",C:C) in C column are numbers, in D column are ("bob" or others) if you wish do not sum all column you can use for example =SUMIF(D2:D6,"bob",C2:C6) Note: Text is case sensitive (Bob is not equal bob)


7

This will work, based on a table with upper-left corner in A1, modify to suit actual: =INDEX(A1:E7, MATCH("Course 3", A:A, 0), MATCH("Pablo", 1:1, 0))


7

There is no need to jump into GAS, standard spreadsheet functions handle this easily. Please paste the following into a cell in your example =iferror(filter($A$2:$A, match($A$2:$A, B2:$B , false)), "no match") The match function generates a cartesian product of the two ranges full of errors except where a match is found. It returns an index into the ...


6

I bashed up a comparison sheet for the formulas between MS Excel 2010 and Google Spreadsheet. Comparison was based on the names only. See snapshot of the current situation: If you want to make sure that Google Spreadsheet files are properly converted and still work in MS Excel 2010, then don't use the following formulas: ADD ARRAYFORMULA CONCAT CONTINUE ...


6

The easiest thing is to copy and paste; the change from c3:al3 to c4:al4, etc, will happen automatically. I would proceed as follows: Enter the formula =average(c3:al3) in AN3. Select cell AN3, press Ctrl-C (or Cmd-C on Mac) to copy it (the cell itself, not the formula from the formula line). Select the range AN3:AN562 (or whichever range you want to ...


5

Simply copy your formula from that cell to each cell below. Copying formulas with relative references (those without $) adjusts them automatically. I think you have your coordinates backwards, though. Cell C2 contains "1000" and cell B3 contains "50", so your formula in C3 should be =MINUS(C2,B3) (or =C2-B3).


5

Solved with: =INDIRECT( ADDRESS( 4, COLUMN() ) ) ADDRESS gives me a string like "$A$1" and INDIRECT makes it a cell reference.


5

First, I'd split the data from column A into two columns. In this case it looks like the length is consistent so it makes it easier (I can demo the other if need be). Get the left chunk with =left(A2,11) Get the right chunk with =right(A2,10) Now you have Element 1: | Value 1.1 To flip the unique elements use =transpose(unique(B2:B7)) Now you have ...


4

Column A can be the recurring cost. Column B can be the number of months in the recurrence. Column C can divide Column A by Column B to get the monthly cost. Then you can sum column C. For your last.fm example: A2 = "9.00" B2 = "3" C2 = "=A2/B2" The your total monthly cost would be the sum of Column C.


4

Answered I received in the Google Support Forum: =counta(iferror(filter(B:B,A:A="name1",B:B="yes")))


3

Figured it out... took me a while... INDIRECT() has to wrap the entire reference. In order to achieve what I needed, the formula looked like this: =INDIRECT("Sheet2!A"&A1)


3

This little script will compare two ranges, as a formula: function COMPARE(array1, array2) { var array = []; for(i=0; i<array1.length; i++) { for(j=0; j<array2.length; j++) { if(array1[i][0] == array2[j][0]) { // the extra square brackets will make it a 2D array, // aligning it vertically array.push([array1[i][...


3

I've been able to find a solution for my problem. Using =ARRAYFORMULA(Sheet1!A1:A) in the top of a column Google Docs will update the whole column, without the need for functions in each cell. Thus, deleted or inserted rows will not cause trouble anymore. I found this answer (by brettathds) at the Google Support forums.


3

In order to "freeze" the formula output on a summary sheet with a script, you can use copyTo method with the option contentsOnly:true. This can be used to copy the values to another place, or to exactly the same cells (thus, overwriting the formulas with their output). Here is a simple script that does this for cells A1:A10 of the sheet named "SummarySheet". ...


3

Try Shift+Ctrl+: and Ctrl+; Those are the keyboard shortcuts for "insert time" and "insert date" respectively. (based on pnuts' comment)


3

Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, ...


3

When you find yourself writing a bunch of similar IF statement, you probably need FILTER. For example, =JOIN(CHAR(10), FILTER(A1:F1, NOT(ISBLANK(A1:F1)))) joins the content of cells A1 through F1, separating them with linebreaks — but the empty cells are skipped as they fail the condition NOT(ISBLANK(...)).


3

You can do this with a combination of the INDIRECT and MATCH functions. In the last column of your table add the following formula in line with the data that you would like to determine the max of: Google Spreadsheet =INDIRECT(ADDRESS([Name_Row], MATCH(MAX([Data_Span]), [Data_Span], 0) + [Column_Offset])) Excel =INDIRECT(ADDRESS([Name_Row], MATCH(MAX([...


3

Short answer Add a new sheet. Add the following formulas to the new sheet Cell A2 -> Headers =OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0) This could be repeated several times, one for each header column, just change the last parameter accordingly. Cell B2 -> data =OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,...


3

Keep the inequality signs going in the same direction: =IF(A1<60, "F", IF(A1<70, "D", IF(A1<80, "C", IF(A1<90, "B", "A")))) The above works fine as long as there aren't too many cases. But if you had many options, C-,C+,B-,B+... it would be better to use a lookup table instead. For example, if you have a lookup table such as the one below, ...


2

I bashed up a comparison sheet for the formulas between MS Excel 2013 and the new Google Spreadsheet. Comparison was based on the names only. See snapshot of the current situation. Screenshot If you want to make sure that the new Google Spreadsheet files are properly converted and still work in MS Excel 2013, then don't use the following formulas. ...


2

This will work. I've used the Named Ranges, Name and From. You don't have to use To at all, since your values are all contiguous. B2 is what I used as the value of the first BMI, you can just drag this formula down the column: =IFERROR(INDEX(Name, MATCH(B2,From),1),INDEX(Name,1,1))


2

For times that span past midnight you cannot simply subtract. Or you'll get a negative number. Start 9:00 PM End 5:38 AM Total -15:22:00 What you'll want to do (where start is column A and End is column B, etc.) is the following formula: =(B2-A2)+1 The one being equal to twenty-four hours. So: 9 pm to 5:38 am = 8:38.


2

Use the following formula to accomplish that: =SUMIF(range;">"&0) The range is of course the one you set it to be !! EDIT 29-03-2013; 23:00 CEST IMPORTANT: This is only valid if you work at least 70 hours per week, each week. It is just as important to know if you have worked less !! Therefore you need to change the way you calculate the "...


2

Your calculation of "This time" / "Last time" expressed as a percentage (by the formatting) is essentially correct, except it isn't working out the increase. So if it was 10 minutes yesterday, and 10 minutes today, that means you did 100% of the time you did yesterday. This is what you are calculating now. So (Today / Yesterday) - 1 will give you the ...


2

There are two things that you need to take into consideration. Consideration The IMPORTRANGE function requires two parameters (strings): spreadsheet key data range The data range needs to correspond to the number of columns you're about to use. In your case, you selected only column B and column C. Therefore, going beyond col2 will end up in failure. ...


2

GoogleTournament() doesn’t work anymore, womp. Mar 18 (2014?) I suspect the above is really just a different way of saying what help does: The behavior of GOOGLETOURNAMENT for the play-in round is broken for 2011 onwards, when the format changed. The behavior is still correct for historical tournaments prior to 2011. but as I doubt it can now be ...


2

This is an old post, but I was looking into this recently and figured I'd chime in. Depending on you needs, the easiest answer I could think of is by creating a drop down menu and referencing a function on another cell. Example: Select A10:A20 > right click > Data Validation Criteria = "List from range" > F2 Formula in cell F2: =mult(now(),1) To ...


2

You need to specify a condition, not just a value, for the FILTER function. So: =FILTER(A2:A6, B2:B6 = MAX(B2:B6)) I have set up a demonstration spreadsheet for this, feel free to have a look or copy it.


2

Try =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where Col18='"&E3&"' and Col19='Installation Booked' ")



Only top voted, non community-wiki answers of a minimum length are eligible