Using Arrayformula when formula contains sumproduct

[Bob Blightman]

I have a google form linked to a google sheet

I have some formulas for other columns in the sheet where the submission has been placed

When a new submission has been made, I want those formulae to be automatically generated for the new row

Most of these I have been able to do using the formula arrayformula, so this for instance, works fine

=arrayformula(if(C2:C="","",B2:B&","&TEXT(weeknum(C2:C),0)&","&text(year(C2:C),0)))

But I have a couple of other more difficult ones which use sumproduct:

=SUMPRODUCT(((($H$2:$H2=H3)*1)*($R$2:$R2="Approved")*1)*($S$2:$S2>AllocationFreeBooking)*1)

Can I wrap an arrayformula around this? I'm struggling with the syntax!

[Swarup Modak]

The short answer is no, you cannot wrap SUMPRODUCT in an ARRAYFORMULA.

Consider using a Google Apps Script onFormSubmit trigger. Instead of a formula that recalculates thousands of rows every time, a script can simply calculate the value once for the new submission and "stamp" it into the sheet as a static number.  

Swarup.