1

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.

| improve this question | | | | |
1

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

| improve this answer | | | | |
  • This just returns 1 or "Oh no", but I want a count indicating how many rows in the range E4-K150 matched the condition. – lemontree Jan 9 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. – marikamitsos Jan 9 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. – lemontree Jan 9 at 21:35
  • That is easy. Just wrap the formula with SUM. Meaning: =SUM(the formula). – marikamitsos Jan 9 at 21:39
  • The way your original question was phrased gave the impression you were stuck on these intermediate steps (the difficult part) – marikamitsos Jan 9 at 21:42
1

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!

| improve this answer | | | | |
  • 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. – lemontree Jan 9 at 14:46

Your Answer

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

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