CODEWITHSUNDEEP
google-sheetsarray-formulascountifcumulative-sum
indigene
indigene

Reputation: 13

formula for countif of columns of cumulative sum of rows

I have this grid:

1   2   3   4   5

2   3   4   5   6

3   4   5   6   7

4   5   6   7   8

I do cumulative totals along rows, so I get:

1   3   6   10  15

2   5   9   14  20

3   7   12  18  25

4   9   15  22  30

Then I count along columns for cumulative totals that are more than 3, so I get:

1   3   4   4   4

Now can I get to the final single row result without creating an interim grid in between (containing the cumulative totals)? I have a lot of data and need to conserve on cells.

Upvotes: 1

Views: 312

Answers (3)

player0
player0

Reputation: 1

you can get single row like this:

=ARRAYFORMULA(TRANSPOSE(MMULT(N(TRANSPOSE(MMULT(A1:E4,
 N(TRANSPOSE(COLUMN(A1:E4))<=(COLUMN(A1:E4))))>3)), ROW(A1:A4)^0)))

0

Upvotes: 0

TheMaster
TheMaster

Reputation: 50472

This is possible, but you need to know the basics of Matrix Multiplication to understand and suit the formula to your array. If A1:E4 had the first Matrix in your example, Solution:

=ARRAYFORMULA(MMULT(--TRANSPOSE(MMULT(A1:E4,--(TRANSPOSE(COLUMN(A1:E4))<=(COLUMN(A1:E4))))>3),ROW(A1:A4)^0))
  • Inner MMULT to do cumulative totals across the row.
  • Outer MMULT to count the total, If the condition >3 is satisfied.
  • Compare COLUMN( or ROWs , Whichever is longer) to get the desired {1,0} array to MMULT.

Upvotes: 1

Ed Nelson
Ed Nelson

Reputation: 10259

You can try:

=ARRAYFORMULA(countif(A:A,">3"))

=ARRAYFORMULA(countif(((A:A)*(B:B)),">3"))

=ARRAYFORMULA(countif(((A:A)*(B:B)*(C:C)),">3"))

=ARRAYFORMULA(countif(((A:A)*(B:B)*(C:C)*(D:D)),">3"))

=ARRAYFORMULA(countif(((A:A)*(B:B)*(C:C)*(D:D)*(E:E)),">3"))

The "*" is a "+" in this context.

Upvotes: 0

Related Questions