I've made an inventory tracking spreadsheet that gets raw data from Google Forms. Forms inputs data into Sheets in a less than ideal way, but there's nothing I can do about that, so I'm stuck post-processing the data. What I need to do is sum a row over a range, not the entire row, and then have that same formula automatically applied to any subsequent rows beneath it so that when new data comes in from Forms, it is automatically processed correctly.
My base formula is
I've tried wrapping it in
=ARRAYFORMULA(SUMIF((S4:S):(BS4:BS), ">.99") and a number of other variations of this, but I either just get errors or I get the sum of the entire array with no repetition of the formula down the rows. I also want to enter the formula in column Q.
I'd really like to be able to do something along the line of
S4:S so that I don't ever have to worry about breaking my formula when there are more rows than I wrote in. The last thing is, whatever the formula ends up being, I need it to be "sum-able" by a pivot table. I had a different variation of my formula originally that automatically duplicated itself like I needed, but it didn't play well with the pivot table and I could not sum my results in the table like I need to be able to.
Thank you very much!