I have a Google sheet with a column of dates and numbers. What I want to be able to do is say: If the date is earlier than 9/1/2021, take the number and multiply by 1, otherwise multiply by 2, then sum all the products. The date and number columns keep getting new information as time goes on.
Now I know that I could create a kind of "scratch-work" column where I have a cell containing =IF(DATEVALUE(A1)<DATEVALUE("2021-9-1"),1,2) * B1 and then apply this formula to the column, and then sum the column somewhere else. However, this is not ideal because every time a new date and number come in I have to reapply the formula to the new scratch-work column's cell.
What I was hoping for was some kind of vectorized operator that would eliminate the need for the scratch work column in the first place. Something like being able to multiply whole columns like =B1:B * 2 or what is more appropriate for my current task, =IF(DATEVALUE(A1:A)<DATEVALUE("2021-9-1"),1,2) * B1:B.
I know you can do this kind of thing in R but haven't seen any vectorized operations in Google Sheets. I'm guessing it's not possible but wanted to ask just in case.
=arrayformula(if(A1:A="","",IF(DATEVALUE(A1:A)<DATEVALUE("2021-9-1"),1,2) * B1:B))