2

This question was asked and as the answer to the specific question was a typo it was deleted:

https://stackoverflow.com/questions/59289065/excel-non-adjecent-cells-as-input-to-array-function-min-and-isblank

Here is the question:

I'm trying to find the minimum of two (non-adjacent) cells per column and sum these for a number of columns (13 in total).

What complicates it is that I'd like the function to treat empty cells as zero.

I can get it to work as long as the cells are adjacent, but when they are not, excel gives a "too many arguments for this function" pop-up.

The formula I have for adjecent cells is this (not exactly pretty, sorry!):

{=SUM(MIN(IF(ISBLANK(P3:P4);0;P3:P4));MIN(IF(ISBLANK(Q3:Q4);0;Q3:Q4));MIN(IF(ISBLANK(R3:R4);0;R3:R4));MIN(IF(ISBLANK(S3:S4);0;S3:S4));MIN(IF(ISBLANK(T3:T4);0;T3:T4));MIN(IF(ISBLANK(U3:U4);0;U3:U4));MIN(IF(ISBLANK(V3:V4);0;V3:V4));MIN(IF(ISBLANK(W3:W4);0;W3:W4));MIN(IF(ISBLANK(X3:X4);0;X3:X4));MIN(IF(ISBLANK(Y3:Y4);0;Y3:Y4));MIN(IF(ISBLANK(Z3:Z4);0;Z3:Z4));MIN(IF(ISBLANK(AA3:AA4);0;AA3:AA4));MIN(IF(ISBLANK(AB3:AB4);0;AB3:AB4)))}

This gives the desired output in the column "person months total".

enter image description here

I have tried to use the CHOOSE function for non-adjacent cells as a test (similar to this question), but this gives the "There's something wrong with this formula" pop-up

=SUM(MIN(IF(ISBLANK(CHOOSE{1;2};P16;P18));0;CHOOSE({1;2};P16;P18)))

So now I'm wondering, can this be done at all? Am I missing something?

I would appreciate the help!

Kind regards, Amy

While the answer to this question was that there was a missing ( after the first CHOOSE, I started working on a simpler version to that hideous long formula and wanted to post it here. So the question is, "Is there a simpler method not using vba?"

Scott Craner
  • 148,073
  • 10
  • 49
  • 81

1 Answers1

2

With the ranges actually being adjacent by rows, but comparing column by column we can use MMULT in an array form. By using MMULT we can create an array of the smallest numbers and 0s and sum them:

=SUM(MMULT(N(IF(A1:E1>A2:E2,IF(A2:E2<>"",A2:E2),IF(A1:E1<>"",A1:E1))),TRANSPOSE(COLUMN(A1:E1)^0)))

This is an array formula and must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

EDIT: way over thought it, this is much simpler:

=SUM(IF(A1:E1>A2:E2,IF(A2:E2<>"",A2:E2),IF(A1:E1<>"",A1:E1)))

Still an array formula.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • @TomSharpe did not know if the OP would be using negative numbers and blank would return `0` which would throw it off. But feel free to add that as an answer as it was not specified. – Scott Craner Dec 11 '19 at 21:39