We can get the following raw data from our project management software: Month Project Billable Time Jan 2022 Project 1 Yes 100 Jan 2022 Project 1 No 10 Feb 2022 Project 1 Yes 80 Feb 2022 Project 1 No 30 Jan 2022 Project 2 Yes 60 Jan 2022 Project 2 No 5 Feb 2022 Project… Read More How to use Google Query to combine multiple rows into a single row with IF & SUM
This is the formula, currently. It outputs 12/30/1899, but it actually returns 0 and since TEXT() is forcing it into a data, this is how it shows. How to have this ARRAYFORMULA() populate the values and empty when there is no date? FORMULA =IF(I4<>””,”Factura (USD)”, ({“Wire Transfer Date”; arrayformula(IFNA( TEXT( VLOOKUP($J$10:$J900, query(Datasets!S3:U,”select S, U”),2,0), “mm/dd/yyyy”)… Read More How to have this formatted data return empty given this is VLOOKUP + TEXT + QUERY in Google Sheets?
I need (and have had difficulty arriving at) a formulaic way to produce the values shown in columns D, E, and F. >Solution : try: =QUERY(A2:B; "select max(B) where B is not null group by B pivot A") update 1: =INDEX(TRIM(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(IF(A2:B="",,A2:B&""), "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9)), "")))) update… Read More Google Sheets: How can I return an array of all values from a column that have a certain corresponding value in another column?
I’ve created a Google Sheets function, with the intended effect of getting the sum of numeric values between two dates, where the value in a different column is equal to a string. The calculation however is having unintended side-effects. The problem is that the function starts out by returning the string value "sum" to the… Read More How can I prevent QUERY from printing its calculation to two rows, and not have it print "sum"?
im trying to come up with an ARRAYFORMULA to substitute formulas on every row =IF(ISBLANK(A2);""; COUNTA(FILTER((Derivatives!B:B);INDEX((Derivatives!D:D);0;0)= left(A2;4);INDEX((Derivatives!F:F);0;0)= "EQUITY CALL"))) Exemple example is almost correct, most of 1 results should be 0, i belive thats caused by COUNTA, not sure, bigger numbers should be correct on my exemple basicly to COUNT number of times Derivatives!B:B, those… Read More ARRAYFORMULA with COUNT
This may turn into a long winded Q so I apologies in advanced. I’m doing some work for a sports league & need some help with advanced Pivot Table help. I’m trying to produce a "Historical Record" spreadsheet for the league. I am able to get my required data into a suitable pivot table (See… Read More Pivot Table Multi-filter Ranking
I am trying to make a simple dashboard to get data from one sheet to show if it is greater than a week in cell B2 =QUERY(‘KPI 14 – 26’!A1:CC14,"select A, sum(F), sum(D), count(D) where A is not null AND A >= ‘"&B2&"’ group by A") This code doesn’t throw an error, but just doesn’t… Read More Google sheets Query >= function not working
Our team has a work calendar on Google Sheets to track company holidays. We’re using a formula that counts the number of times the word ‘Hol’ appears in a row. The below snippet works, but I am also looking to count additional terms such as BH and Half. How would I add additional words to… Read More Adding additional text criteria to INDEX Query Flatten
I’m trying to use vlookup between two tabs in a sheet where the data that exists in a single column denoted by "type" (tab B) needs to be transcribed into separate columns by type (tab A). Edit: For example, tab B has the "raw" data listed in just three columns: the type, the ID, and… Read More Google Sheets: VLOOKUP based on same-column value of reference sheet?
So, right now, the formula below successfully queries data from another file, butthis based on a column’s data as the criteria. The need is to have more than one criteria and I’m not sure if thecolumn to be compared against needs to come in as an additional query, together with the first query…so I’m lost… Read More How to apply multiple criteria when using VLOOKUP+QUERY+IMPORTRANGE?