# Trying to get array formula to work on Sheets

I am trying to to use the Array formula that includes a minus and divide value. Here is my formula:-

`IF(J3:J="",,SUM(((B3:B+C3:C)+(E3:E+F3:F))-\$X\$3)/\$X\$3)`

I tried inserting MINUS( before the SUM but this didn't evaluate correctly. As far as the Divide goes I have tried to fit this in next to the MINUS, but this through up an error. I have read that minus and divide are not like by array formula but there is a workaround. could I ask for some help please in getting the above formula to operate as an array.

• Welcome to Web Applications. Please edit the question to add some sample input data, the number of rows having values, sheet number of rows (in other words tell if there empty rows below the data) and the expected result for the sample data.
– Rubén
Jan 5, 2022 at 16:28
• Remove the word `SUM` from your equation, leaving everything else the same (including all sets of parentheses), and see if you get the result you expect. Jan 5, 2022 at 17:06
• I have added a sheet for your viewing. The suggestion you offered, I trialled and found the sum part of the formula worked the rest did not work. hope this helps. docs.google.com/spreadsheets/d/… Jan 5, 2022 at 20:13
• Hi. Thanks for including your spreadsheet. 1) It references `\$O\$3` (instead of `\$X\$3`), would you please edit the cell reference in your question formula for consistency. 2) There are many possible ways for this formula to be written (with and without `ARRAYFORMULA`) would you please edit your spreadsheet to clearly include the answer that you are trying to calculate. 3) Column J includes a value in each row, would you please describe your expectations for the formula if there was not a value in each row. Jan 6, 2022 at 2:23
• Hi Tedinoz 13 Thanks for taking a look at this issue I have. I have amended the sheet and changed the reference to x and not o. Also I have listed in red the expected values. I would prefer to use array formula as the sheet is plus 700 rows and growing. the Column J will always be occupied i.e. it will hold the date that the row was edited. the date will always be from Monday too Friday (5 days) Jan 6, 2022 at 17:12

`=arrayformula(IF(J3:J="",,((B3:B+C3:C)+(E3:E+F3:F)-\$O\$3)/\$O\$3))` 