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 =SUMIF(S4:BS4, ">.99"). I've tried wrapping it in =ARRAYFORMULA(SUMIF(S4:BS4, ">.99") and also =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!

  • =ARRAYFORMULA(SUMIF((S4:S):(BS4:BS), ">.99") worked for me. So also does a more straightforward version: =sumif(B4:I,">.99"). You said that "I also want to enter the formula in column Q." It's not exactly clear what you mean by this; I'm guessing you mean that you want to sum column Q?? Have you tried =sum(D4:D)?
    – Tedinoz
    Apr 5 '20 at 9:36
  • Thanks for the response. What I get with those formulas is the sum of the entire sheet essentially for that given range, but what I need to do is get the sum of the row S4:BS4 for example, and then have the formula automatically be applied to any subsequent rows so that when new data comes into the next row, it all gets summed as well. Essentially I need =SUMIF(S4:BS4, ">.99"), then =SUMIF(S5:BS5, ">.99"), then =SUMIF(S6:BS6, ">.99") and so on, but so I don't have to manually enter that formula into every new line. For "Q", I just meant if my range is S:BS, I just want the sum in Q. Apr 5 '20 at 13:26
  • So to clarify; you want to insert a formula in column Q which sums the values on a single row between Column S and Column BS where the value is greater than "0.99". And the formula in Column Q should automatically expand into new rows as they are populated by relevant data.
    – Tedinoz
    Apr 5 '20 at 22:46
  • Yes, that's exactly what I'm trying to accomplish; thank you! May 1 '20 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.