# COUNTIF with relative cell range in condition

I want a Google sheets formula along the lines of

``````=countif(a4:a150;and(sum(e4:j4)<132;sum(e4:k4)>=132))
``````

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.

You can use the following formula in cell `A4`.

``````=ArrayFormula(IF(
ARRAYFORMULA(SUMIF(IF(COLUMN(E4:J4),ROW(E4:J8)),ROW(E4:J8),E4:J8)<132)*
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.

``````=IFERROR(ArrayFormula(IF(
ArrayFormula(MMULT(E4:J8,TRANSPOSE(SIGN(COLUMN(E4:J4))))<132)*
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. 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. 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. Jan 9, 2020 at 21:35
• That is easy. Just wrap the formula with `SUM`. Meaning: `=SUM(the formula)`. 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) Jan 9, 2020 at 21:42

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

``````=SUMPRODUCT(ARRAYFORMULA((E4:E150+F4:F150+G4:G150+H4:H150+I4:I150+J4:J150)<132),ARRAYFORMULA((E4:E150+F4:F150+G4:G150+H4:H150+I4:I150+J4:J150+K4:K150)>=132))
``````

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

Hope that helps!

• 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. Jan 9, 2020 at 14:46