I probably don’t expect an answer to this because I’ve been trying for months to figure it out. I just wanted to see if anybody had any tricks they knew about.
I have a sheet that I’ve been trying to make more efficient. Basically the first 3 columns are generated from a query. That part is fine. Then I have several formulas in other columns to figure out totals and what not from other sheets. Several of my formulas were queries that I had to drag down about 1000 rows to allow for growth of the sheet. Last night I converted most of them to SUMIF formulas which cut down the processing time quite a bit. The SUMIF formulas were easy because my condition was looking for an exact match.
Now I have 2 columns that still use queries and have the formula dragged down 1000 rows, but I cannot figure out a way to convert them to a formula that works with an arrayformula.
The first scenerio is a dynamic html table (I don’t need the don’t use html speech) that concatenates the opening and closing table tags, with prebuilt rows from another sheet based on a condition. This is what I’m currently using:
=IF(LEN(C2)=0, "", CONCATENATE("<table width=100%>" , query(Lessons!A$2:Z, "select J where N='" & A2 & "-" & C2 & "' order by A, D ",0), "</table></br><div style=""text-align: right"">TOTAL: ", DOLLAR(G2, 2), "</div>"))
Basically I want something that functions like a SUMIF or a VLOOKUP, but returns multiple rows concatenated together. If only there was a function like CONCATENATEIF.
The second scenario is a total value based on rows that sort of, but don’t completely match certain values in the particular row. I’m trying to get a total of unpaid bills for a particular student/coach combination prior to the billing date of the current row. A SUMIF works great when looking for an exact match, but I what I really need is something like a SUMIFS for multiple conditions, but works with an arrayformula. This is what I’m currently using:
=IF(LEN(M2)=0, "", IFERROR(DOLLAR(QUERY(A$2:N, "SELECT SUM(J) WHERE M='" & M2 & "' AND N <'" & N2 & "' LABEL SUM(J) ''")),"Paid"))
I want to find all records that match the student and coach, but where the billing date is prior to the current billing date. Like I said, basically a SUMIFS, but something that works with arrayformula.
I’ve never been able to figure this out and I know I’m not the only one. What I have works fine, but I have so much processing time in the google sheet, that it’s caused some glitches with the glide app because it can’t sync up properly while the sheet is processing. I’ve mitigated it by using IMPORTRANGE to move data back and forth between another sheet and letting the second sheet handle the processing. This has allowed the glide sheet to function properly. Ideally, if I could figure out these formulas, then I could move everything back into the one Glide sheet.
If anybody has any google sheet tricks up their sleeve, I would much appreciate it. Again, I don’t know if there is an answer other than using queries and dragging the formula down, but I thought I would try.