Google-sheets – COUNTIF with relative cell range in condition

google sheetsgoogle-sheets-arrayformula

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.

Best Answer

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")

Related Topic