# How to use Google Query to combine multiple rows into a single row with IF & SUM

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

# How to have this formatted data return empty given this is VLOOKUP + TEXT + QUERY in Google Sheets?

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?

# Google Sheets: How can I return an array of all values from a column that have a certain corresponding value in another column?

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?

# How can I prevent QUERY from printing its calculation to two rows, and not have it print "sum"?

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"?

# ARRAYFORMULA with COUNT

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

# Pivot Table Multi-filter Ranking

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

# Google sheets Query >= function not working

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