Below is actually everything that is needed to answer this question.

enter image description here


You can use the following formula instead:


enter image description here

Functions used:

| improve this answer | |
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Jul 21 at 21:13
  • You are my hero.. – Fabian Jul 22 at 14:26
  • Glad I could help :) – marikamitsos Jul 22 at 20:33

Another approach:



FILTER can take an array as its first argument. The tricky part is that every element of that array must have the same dimensions. While A2:A4 is a 3x1 range, SUM(1,3) is a 1x1 range. But this can be solved by multiplying SUM(1,3) by a sort of placeholder 3x1 array: ROW(A2:A4)^0. Since every element in A2:A4 has a row number, and any number to the zero power (^0) is 1, this just signals Google Sheets to give you SUM(1,3) "multiplied by 1 for three rows. And that makes the two elements of that curly-bracket array match up with everything else.

| improve this answer | |
  • Really cool to see. How do you know all this? Could you recommend a course? – Fabian Jul 22 at 14:32
  • ROW(A2:A4)^0 Why can't I just fill in 1? This is hard to understand. – Fabian Jul 22 at 15:17
  • Again, every part of an array must have the same dimensions. So you can't multiply a range of values by a single number, like A2:A4 x 1, because A2:A4 is three rows by one column, while 1 is just 1 row by 1 column. You need a way to apply the x1 to every element of A2:A4 (like A2:A4 x {1;1;1}. I could have used something else, like IF(LEN(A2:A4), SUM(1,3),"") or IF(ROW(A2:A4), SUM(1,3),""). You just have to have some way of applying SUM(1,3) to every part of A2:A4 so they take up the same number of rows and columns. – Erik Tyler Jul 22 at 15:42
  • Thank you for your reaction. I'm gonna take another good look at it. I want to understand. – Fabian Jul 22 at 17:39

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.