I want a Google sheets formula along the lines of


I.e., for each of the rows 4-150 (column A is not relevant), if the sum of columns E-J in that row is <132 and the sum of columns E-K in that row is >=132, then count that row with 1; in the end return the amount of rows that satisfy the condition.

Pseudo code:

count = 0
for 4 <= i <= 150:
    if (sum(Ei:Ji)<132 and sum(Ei:Ki)>=132):
       count = count+1
return count 

However, countif does not accept complex conditions. I've been fiddling around with arrayformula and nestings of sum and countif for a while but can not find out which combination yields me the desired function structure that iterates over all rows, then checks the sum over a column range relative to that row and counts the result.

2 Answers 2


You can use the following formula in cell A4.

    ARRAYFORMULA(SUMIF(IF(COLUMN(E4:K4),ROW(E4:K8)),ROW(E4:K8),E4:K8)>=132),1,"Oh no!"))

(The above formula assumes data is in E4:K8. Please adjust it to fit your data.)

The following formula can be used as an alternative that also warns you when and if there are any empty cells in your data.

    ArrayFormula(MMULT(E4:K8,TRANSPOSE(SIGN(COLUMN(E4:K4))))>=132),1,"Oh no!")),"missing value")

  • This just returns 1 or "Oh no", but I want a count indicating how many rows in the range E4-K150 matched the condition. Commented Jan 9, 2020 at 20:57
  • As for the "Oh no", it can be changed to whatever you like. Furthermore. Sorry but I do not understand your edit: "in the end return the amount of rows that satisfy the condition." In the end of what? Your original question was "...then count that row with 1". This is what both formulas do. Please share a test sheet with expected results. Commented Jan 9, 2020 at 21:30
  • In the end of evaluating the whole formula. The 1/0 is the intermediate result for each row. Each row is just one iteration in the countif, which is supposed to be the outer function. Commented Jan 9, 2020 at 21:35
  • That is easy. Just wrap the formula with SUM. Meaning: =SUM(the formula). Commented Jan 9, 2020 at 21:39
  • The way your original question was phrased gave the impression you were stuck on these intermediate steps (the difficult part) Commented Jan 9, 2020 at 21:42

As you're only summing a few columns, you could do something like this:


Using ARRAYFORMULA with functions that already take a range gets overly complicated. I think this solves it for you fairly easily.

Hope that helps!

  • 1
    This will probably do the job, but I was hoping that there would be a more generic solution using the sum function, since I have several of these conditionals and some of the sums will be quite long. Commented Jan 9, 2020 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.