If I have spreadsheet that contains a row like the following with 4 columns
Net Cashflow 0.00 -80.00 500.00
I know I can get the whole row returned {0.00, -80.00, 500.00} with the following array formula:
=INDEX(B1:D3,MATCH("Net Cashflow",$A:$A,0),0)
But how would I get just the non-zero values returned as a row (i.e. Net Cashflow <> 0)
You can use the following formula:
=IF(INDEX(B:B, MATCH("Net Cashflow", A:A, 0)) = 0, "", INDEX(B:B, MATCH("Net Cashflow", A:A, 0))&", ")&IF(INDEX(C:C, MATCH("Net Cashflow", A:A, 0)) = 0, "", INDEX(C:C, MATCH("Net Cashflow", A:A, 0))&", ")&IF(INDEX(D:D, MATCH("Net Cashflow", A:A, 0)) = 0, "", INDEX(D:D, MATCH("Net Cashflow", A:A, 0)))
Consider the following table:
| ------------------------------------- |
| A | B | C | D |
| -------------|------|--------|--------|
1 | Net Cashflow | 0.00 | -80.00 | 500.00 |
| ------------------------------------- |
The result of the formula would return:
-80, 500
Supposing for the sake of argument you wanted to multiply the amounts in your row together. You wouldn't want to include zeroes because if you did the answer would always be zero, so you could choose to set them to 1 instead:
=PRODUCT(IF(INDEX(B:D,MATCH("Net Cashflow",$A:$A,0),0)=0,1,(INDEX(B:D,MATCH("Net Cashflow",$A:$A,0),0))))
entered as an array formula.
This is just applying your formula along the lines suggested by @Jeeped.
But to answer your actual question, 'how would I get just the non-zero values returned as a row?' - the answer is, 'with difficulty'. It is possible because the Index function can be coerced into returning an array as in this answer
You can use the SMALL function to get only the columns containing non-zero values, but to do so you would need to create an array containing (in your example) two elements. One way of doing this is using Indirect. If you put all this together you get
=PRODUCT(INDEX(B:D,
MATCH("Net Cashflow",$A:$A,0),
N(IF({1},SMALL(IF(INDEX(B:D,MATCH("Net Cashflow",$A:$A,0),0)<>0,COLUMN(B:D)-1),ROW(INDIRECT("1:"&COUNTIF(INDEX(B:D,MATCH("Net Cashflow",$A:$A,0),0),"<>0"))))))
))
So it is possible, but...really this is how not to do it.
