New answers tagged

0

I used this and solved as well =countifs(A1:A,"<>")+countifs(A1:A,"",B1:B,"<>")


1

You would need to use a formula with the SUBSTITUTE function =SUBSTITUTE(D8,"https://drive.google.com/open?id=", "https://drive.google.com/uc?export=view&id=")


0

A simple query formula is all you need for each tab you create. =QUERY(JiraData!A1:C, "where B=2",1) Where JiraData is the name of your data tab, B is the column and 2 is the story points. Functions used: QUERY


2

Please use =QUERY(INDEX(A1:A&B1:B),"select count(Col1)",0)


1

Try this: =COUNTA(IFERROR(FILTER(ROW(A:A),A:A&B:B<>""))) ROW(A:A) gives us "something" to count. By concatenating each row-cell of A:A with the corresponding row-cell of B:B, we form one string, which we can evaluate as one string and thus count as one string.


0

It's unclear what "I love the transpose date function above" means. But given the context, I'm guessing that you mean DATEDIF. If that is correct, there is no equivalent for time. However, you can "trick" DATEDIF into working with time by multiplying each time by 24 (hours in a day). For instance, if check-in time were in A2 and check-out ...


0

I assume that you want to do this for more rows than just row 2 (i.e., that you will want to do this for rows 2 and downward). If that is correct, make sure you have three empty columns side by side in the 'Cost Analysis' sheet and then try this formula in the upper leftmost corner of that three-column range: =ArrayFormula(IFERROR(FILTER(IFS(Responses!A2:C=&...


0

You mention I would like to get a column with the difference between H and G. So I would be filled with -02:30. The formula =H2-G2 will work fine in Google Sheets once you format as Duration by going to Format - Number - Duration


1

You can address your issue by using 2 similar formulas as shown in the tab I have created in your spreadsheet. The most challenging of the two is in cell P4 where we calculate the times =INDEX(IFERROR(REGEXREPLACE(FLATTEN( SPLIT(QUERY({C4:C&"@"},"where Col1 is not null",0) ,CHAR(10))),"@"," ...


2

This comes down to all those algebra classes no one thought they'd ever need in real life. I can't teach algebra here, but the basics: X = base year (from which all other years follow, i.e., your B6 amount) Let B = your discount. Rather than thinking of it as what is taken away, however, we should think of B as what remains. This leaves us with XB (which, in ...


0

To get results by year and month, try the following in E1 =QUERY(ARRAYFORMULA({TEXT(A1:A,"yyyy-mmm"),B1:C}), "select Col1, count(Col3) where Col3=TRUE group by Col1 label count(Col3) 'Half/month' ",1) OR To get results by just month, try the following in G1 =QUERY(ARRAYFORMULA({TEXT(A1:A,...


0

Assuming that your drop-downs run from D2:D and E2:E and that you want to process for F2:F entirely, make sure F2:F is entirely empty and then add the following to F2: =ArrayFormula(IF(D2:D&E2:E="",,IFERROR(IF(LEFT(D2:D)="A","No Need",IF(LEFT(D2:D)="W",IF(LEFT(E2:E)="A","Slight Need","No ...


0

Update based on your comment: Use this as a custom filter formula: =REGEXMATCH(F2,"(?i)Amazon.*Twitter|Twitter.*Amazon") As a google sheets formula: =FILTER(F2:F,REGEXMATCH(F2:F,"(?i)Amazon.*Twitter|Twitter.*Amazon"))


1

There are many ways to do this. For simplicity's sake, I recommend this: =FILTER(F2:F,REGEXMATCH(F2:F,"Amazon"),REGEXMATCH(F2:F,"Twitter")) It should be easy to understand and add to as needed.


1

Please use the following FLATTEN formula =FLATTEN(A2:C4) OR ={"Questions";FLATTEN(A2:C4)}


0

Try this formula: =query({importhtml("https://fbref.com/en/share/SxTNE","table",0)},"Select Col3") There are several reasons why your formula was not successful: "special" characters vs "normal" characters: often when formula are copied from the web, they can include characters that are not conventional ...


1

You can use an Arrayformula to achieve your goal. You don't need to drag down a formula nor specify cells. This will work for every correct calculation in column A: =Sum(Arrayformula(Iferror((Abs(A2:A8)+A2:A8)/A2:A8,""))) or ignore iferror if you are sure A2:A8 contains numbers (and not text or blank elements): =Sum(Arrayformula((Abs(A2:A8)+A2:A8)/...


0

I wound up expanding the gaps of the LOOKUP of the first 3 eliminating the need for the rest. It simplified the formula.


1

Explanation: Use a query to get the last N entries of a particular name and then get the average based on column C. The trick here is to reverse the matrix: sort(A1:C5,ROW(A1:A5),False) so you get the last N rows. Solution: =INDEX(QUERY( QUERY(sort(A1:C5,ROW(A1:A5),False),"select * where Col1 contains 'John' limit 2"), "select avg(Col2),...


0

Please use the following =ArrayFormula(IF(A2:A="",, REGEXREPLACE(TEXT(A2:A,"#"),"(\d{4})(\d{2})(\d{2})","$1/$2/$3")^1)) (adjust the range to your needs and format as date) How the formula works: Because REGEXREPLACE takes only text values we use TEXT to turn the numbers to text, edit them and then turn them ...


1

Please use the following query formula (adjusting ranges to your needs), instead of the one you already use. =QUERY({P1:R},"where Col2 matches 'admission|PCI|pending' ",0) Functions used: QUERY


1

Please use the following query formula (adjusting ranges to your needs), instead of the filter one you already use. =QUERY(sort(P1:R,match(Q1:Q,{"Pending","admission","PCI"},0),TRUE), "where Col2 matches 'admission|PCI|pending' ",0) Functions used: QUERY SORT MATCH


1

Please use the following query formula (adjusting ranges to your needs), instead of the filter you already use. =QUERY({P1:S},"where Col2='admission' AND Col4<>'' OR Col2='consultation' ",0) Functions used: QUERY


2

Addressing strictly what you've requested in your post, I added a sheet ("Erik Help") with a custom conditional formatting rule applied to Column A: =AND(LEN(A1),ISERROR(REGEXEXTRACT(A1,"^\d{4}\s\d{3}\s\d{3}$"))) This will highlight any entries in Column A that are not in the format "#### ### ###". You can view this rule by ...


3

You mentioned How can I use a filter or conditional formatting to find all numbers that are NOT in this format (#### ### ###). Please use the following conditional-formatting rule =IF(A1<>"",(IF(ISNUMBER(A1),TRUE,NOT(REGEXMATCH(A1,"^\d{4} \d{3} \d{3}$")))))


0

I believe you are looking for a simple SUMIF: =sumif($E$2:$E,E2,$C$2:$C)


2

First substitute the white space. Paste this to Cell B2: =SUBSTITUTE(A2, " ", "") After that make your number to exactly 10 digit. Paste this to Cell C2: =IF(LEN(B2)=9,"0"&B2, IF(LEN(B2)=8,"00"&B2, IF(LEN(B2)=10,B2))) Lastly add the space back to the right place.Paste this to Cell D2: =LEFT(C2,4)&" &...


2

Solution: This is the correct way to use INDIRECT: =INDIRECT("'IMPORT - CURRENT JOB'!EJ"&M8) Example:


0

Try this in Sheet1: =COUNTA(Sheet2!A2:A)/COUNTA(Sheet2!B2:B) You will probably need to then format the result as a percentage (Format > Number > Percent).


2

Try this: =query({query(A2:B7);query({D2:E5},"select Col1, -1*Col2 label -1*Col2 ''")}, "select Col1,Sum(Col2) group by Col1 Label Sum(Col2) 'Sum'")


0

Instead of using multiple FILTER functions, use only one: =Filter({ 'Spain Review'!BN2:BQ15; 'French Review'!BN2:BQ15; 'German Review'!BN2:BQ15; 'Italian Review'!BN2:BQ15; 'Prem Review'!BN2:BQ15 },{ 'Spain Review'!$BN2:BN15<>""; 'French Review'!$BN2:BN15<>""; 'German Review'!$BN2:BN15<>""; 'Italian Review'!$BN2:...


0

Create a new column called "Average Y" In the first cell, use the following formula: =AVERAGEIF(A:A,A1,B:B) Where A is your column of X values and B is your column of Y values. In the same table as OP, result looks like: And your resulting graph looks as expected (no duplicate points):


2

Here is another approach: =ArrayFormula(VLOOKUP(TRANSPOSE(SPLIT(JOIN("",REPT(ROW(A:A)&"@",A:A)),"@",0,1)),{ROW(A:A),B:B},2,FALSE)) How It Works =ArrayFormula(...) signifies that the formula will process an entire range and not just one cell. Now, working from the inside out... REPT(ROW(A:A)&"@",A:A) will add ...


2

Please use the following =QUERY(ArrayFormula(flatten(IFERROR(SPLIT(REPT(B1:B&"@",A1:A),"@")))),"where Col1<>'' ") How the (undocumented) flatten function work. When we use flatten on an array of cells, it transposes the array row by row into a single column. Please notice the difference between flatten and TRANSPOSE +-...


1

If I understand your question correctly you are trying to find the median for each year. Please use: =MEDIAN(QUERY($B$2:$C$7,"select C where B="&B10&"",0)) Functions used: QUERY MEDIAN


0

@shershen, for the sake of my suggestion here, I will assume the following: That your comma-separated choices lists runs A2:A That everyone will make exactly three choices (no more, no fewer) That first choice = 3pts, second choice = 2pts and third choice = 1pt That being the case, try the following formula: =ArrayFormula({"#"&SEQUENCE(...


1

Please use the following. =IF(LEN(A2),IFERROR(D2*GOOGLEFINANCE("CURRENCY:"&B2&"USD"&""),D2),) Copy down till it meets your needs. Functions used: IF LEN IFERROR GOOGLEFINANCE


0

Please use the following formula =QUERY(flatten(ArrayFormula(IFERROR( REGEXREPLACE(SPLIT(A2:A11,", "),"("&REGEXREPLACE(A2:A11,", ","\)|\(")&")","$1$2$3"&" "&COLUMN(A1:C1)&" place")))), "select Col1, count(Col1) group by Col1 ...


Top 50 recent answers are included