New answers tagged

-1

This formula sums all the sums in column C by date: =sort(transpose(QUERY(inservice!A3:C200, "select SUM(C) where A is not null pivot A", 0)), 1, FALSE) Result: You might want to check the QUERY function, which is what solves your problem. I used TRANSPOSE and SORT in order to keep your presentation style.


0

Google Sheets doesn't have a built-in function that directly returns something to tell us if a cell is merged or not. Perhaps the closer function is CELL(info_type, reference) with the first parameter specifying the type of information that should be returned. Unfortunately it doesn't include an "info_type" for merged/un-merged. On the other hand, it's a ...


0

Alternative 1 Instead of using comparison like B2=Casula!$A:$A use NOT(ISNA(MATCH(B2,Casula!$A$2:$A,0))). Example =IF(NOT(ISNA(MATCH(B2,Casula!$A$2:$A,0))),"Casula",IF(NOT(ISNA(MATCH(B2,Miranda!$A$2:$A,0))),"Miranda",IF(NOT(ISNA(MATCH(B2,Parramatta!$A$2:$A,0))),"Parra",IF(NOT(ISNA(MATCH(B2,Leichhardt!$A$2:$A,0))),"Leich","NOT IN USE")))) NOTE: Using long ...


1

Well, it's clunky, but this works: INDIRECT("R" & ROW() & "C" & COLUMN(),false) e.g. = AND(INDIRECT("R" & ROW() & "C" & COLUMN(),false) <> "Financial",INDIRECT("R" & ROW() & "C" & COLUMN(),false) <> "Income") I could make it a lot neater if I could pack some of this into a custom function, but I can't seem to ...


0

The Google Sheets API documentation includes an article about Date and Number formats. According to this article, the default conditions that could be used are for negative, positive, and zero . They could be customized to something like low, high and in between but in this case, adding ordinal suffixes, this isn't enough as the rules are more complex. The ...


0

From rev. I'd like to replace occurences found in Column A (Cell A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098. To replace a value automatically (just after it was typed) on Google Sheets use an onEdit, simple or installable, Google Apps Script triggers. Example: function onEdit(e){ var ...


1

=OR(D$2="Sat",D$2="Sun") is the custom formula I use to change column colour if the header (line 2 in the sheet) is Sat or Sun. To use a full word, just type it inside the quotation marks. The dollar sign $ locks the formula to row 2 over the range. So if you put the Dollar Sign $ in front of the column letter, it will check the column instead of the row.


1

You have to "stack" your columns. You can do that using the following formula: =FILTER({A4:A10;C4:C10}, MATCH({B4:B10;D4:D10},"RINGO")) EDIT (following your comment): =SORT(FILTER({A4:A10;C4:C10}, MATCH({B4:B10;D4:D10},"RINGO")))


1

On the first formula the concatenator operators & are missing. The correct syntax is =QUERY(sheet1!A:F,"select count(D) where D=7 and A = '"&B2&"'",0) Regarding the second formula B isn't a valid reference. To count the number of times the value 7 exists for each user you could use GROUP BY if you want that the row header be calculated ...


1

What you need is this: ="en:" & B2 & ", es:" & C2 & ", it:" & D2 & ", pt:" & E2 EDIT (following your comment) To preserve/include quotation marks("), escape/prepend your "s with another ". In your case you should use: ="""en"":""" & B2 & """, ""es"":""" & C2 & """, ""it"":""" & D2 & """, ""pt"":""" &...


1

In C2 enter this formula : =filter( ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A)), ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A))<>"" ) Breakdown of the formula : =REGEXMATCH(A2,TEXTJOIN("|",true,B2:B)) Regexmatch returns TRUE or FALSE if A2 contains any of the words in column B. TEXJOIN ...


0

There is my idea. But I think it's not a cute =REGEXEXTRACT( REGEXREPLACE(A1,".*((Jul|Jun) 2019).*", "⚛$1"), "[^⚛]+$" ) for the string 0dfsa sd - Jun 2019 sadfdf sdf - Jul 2019 asdfasdf - Augx There is another regexp ".*(Jun 2019|Jul 2019).*" It also works fine. My final fantasy is =IF( ISERROR( REGEXEXTRACT( A1, "- May 2019| - Jun ...


0

There are no errors because there's no data : IMPORTXML retrieve every data corresponding to a specific Xpath query and will not display anything if there's nothing. Conclusion : IFERROR is useless here. However what you can do is concatenate your IMPORTXML queries using | like this : =IMPORTXML(A4,"//h2[@class='vehicleTitle margin-x']/a/span[@class='...


0

In your Sheet 02 you should use: cell A2: (Your Search Tag) cell B2: =QUERY('01'!A2:D4, "select B,C,D where A like '%"&A2&"%' order by B",0)


0

You can use IF with a REGEXMATCH function or FILTER for the same result without QUERY. If you really want a Query, here's a fix : 1 - Try this instead =QUERY('01'!A2:D4, "select B,C,D where A = '"&A2&"' order by B") 2- Can you explain your needs a little more, with a screenshot of expected result perhaps ?


0

If what you want is to import to Test2 from Test1 a range of cells (A1:A2), the formula should be: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1HLuD8Y4ApxXc2Zt3osHkTTsdWBjpiOIXFSFOdslca_Q/edit", "Sheet2!A1:A2") If on the other want to import just a single cell (A1) the formula would be: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/...


1

By default when you use HH:MM it's a TIME type of format Example : Format is TIME : 01:00:00 Format is DURATION : 25:00:00 Test : 01/01/2019 + 25:00:00 = 02/01/2019 01:00:00 with the TIME format. 01/01/2019 + 25:00:00 = 1043209:00:00 when using the DURATION format. Conclusion : try using HH:MM:SS to avoid futur format problems with ...


1

You can ask Sheets if both of the cells are not blank this way: =IF(AND(NOT(ISBLANK(A2)),NOT(ISBLANK(B2))), "here","not here")


0

I think LOOKUP is maybe overkill to do this ? Try instead this : =ROUND(range/2,0) Example 1 Entry Result 10 | 5 9 | 5 8 | 4 7 | 4 6 | 3 5 | 3 4 | 2 3 | 2 2 | 1 1 | 1 Formula used in B1 : =ARRAYFORMULA(ROUND(A1:A10/2,0)) Edit : if you want to use LOOKUP, try to ...


1

Instead of using the Google Sheets UI filters, use a formula to filter the results. To do this you could use functions like FILTER and QUERY


1

I found a way to do this. First, I brought all the unique values from Columns D-G of Sheet1 to a single column in Sheet 2 with this: =SORT(UNIQUE({'Sheetname'!D2:D;'Sheetname'!E2:E;'Sheetname'!F2:F;'Sheetname'!G2:G})) So that sorted the names into alphabetical order (Col B) and number of instances of each in the next column (Col C). Then, on that Sheet2, ...


3

Instead of date 'TODAY()' use date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'" NOTES: The last " should be placed in a proper place in order to have a text value as the second argument of QUERY. 'TODAY()' doesn't returns Today's date, it returns a string with the word TODAY followed by parenthesis. Try this =ARRAYFORMULA(QUERY({A3:H, HOUR(A3:A)}, "...


0

Try REGEXPLACE instead : =REGEXREPLACE([source value],TEXTJOIN("|",TRUE,'list of values to look for the source value in'!A:A),"static string")


2

Use VLOOKUP(). Assuming your lookup table is in H1:I50, you'd put the following in B1: =VLOOKUP(A1,H1:I150,2,FALSE)


Top 50 recent answers are included