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.
I’ll try to throw together a sheet in the next few days that can be copied. Mine is a little odd because it pulls some data from a separate spreadsheet using importrange. It’s the imported data that my formulas and other sheets are using.
Use SUMIF instead of SUMIFS. It’s not as commonly known, but SUMIF does indeed support multiple conditions. I avoid SUMIFS specifically because I need to use ARRAYFORMULAs so much.
Ok, I know about the joining trick, but I need one condition for ‘equal’ and the second condition for ‘less than’ by comparing dates. From your description, it may work. I’ll have to give it a shot tomorrow. Thanks for the tip!
PS, to clarify, I misread your first reply. I thought it said “doesn’t”.
Here is a scrubbed version of my sheet with all of the vital supporting sheets. The sheet is question is InvoiceBillingList. The Yellow columns are the ones that I’m trying to make into an arrayformula. Right now both columns contain query formulas that have been dragged down 1000 rows to allow for growth. Those are the ones that eat up a lot of processing time. I think it would be more efficient if they could run as an array formula of some sort.
@John_Cabrera I attempted your formula and several variations of it in the Green column, but I wasn’t having any luck.
The InvoiceMarkdown column uses Concatenate and Query to built an html table of all of a student’s lessons for a particular coach’s Billing Cycle. The query has a pretty straight forward ‘where’ clause. I’m trying to built a concatenated list of all lessons that match the student/coach/billingcycle.
The ‘Amount Owed All’ column uses Query to Sum all amounts owed from the same sheet where the student/coach matches and the ‘From’ billing cycle date, for that student/coach, is prior to the From Date in the current row. This is where I’m trying to figure out the 2 conditions within a SUMIF.
If anybody is bored and want’s to take a crack at this, I’d appreciate it. The values currently in the Yellow columns are calculating correctly, so I’m using the green columns to try to come up with better formulas to match.
@Jeff_Hager If you can’t figure it out, I doubt any of us can. I tried and failed
My solution however would be to write some google script code that would add those two “non arrayformulas” you have in column H and O as new rows are appended by your Glide app. That would solve your problem and avoid the need to do those calcs in other related sheets with a bunch of extra rows.
@George_B I appreciate the effort. Sometimes it feels so close, but sheets just can’t do some things that seem so simple.
A script may be my ultimate solution. Do you see any advantage to creating a custom function, within an arrayformula, over a triggered new row event that just adds the formula to new rows?
I have not been able to get a custom function to work inside of an arrayforumla. I have written a custom function, that returns an array, to populate the entire column for one of your columns. The problem is with refresh of the data as new rows are added. That can be solved somewhat by passing in ranges that fake out the calc to recalc. But when the number of rows gets large it becomes an issue. So I have settled on adding the formulas as new rows are appended. That is what I did with the two columns that have those formulas in your sample sheet.
Good to know. I played around with a very basic custom function the other day, but the load time seemed concerning. Trigger on new row it is! Thanks again for looking.
