Excel Array Formula

  • Thread starter Thread starter John Telly
  • Start date Start date
J

John Telly

I have an array formula in a cell which pulls data from
another sheet like this: {SUM(IF($B$4='Forecast Input'!
$A$2:$A$136,IF($A15='Forecast Input'!$B$2:$B$136,'Forecast
Input'!J$2:j$136)))}. This array formula works fine.
In this same cell, I also want to add the following
formula: W14+(v27/36). But when I add this to the end of
the array formula, the {} around the array formula
disappears and it is no longer an array formula.
Question: how can I add W14+(v27/36) to the end of the
above array (in the same cell as the array formula)and
make it work?
John
 
John

are you pressing Ctrl-Shift-Enter to enter the formula ? If not, that's
your problem; you need to re-enter it as an Array Formula.

Regards

Trevor
 
Hi John
instead of using this array formula you may use the following non array
formula:
=SUMPRODUCT(('Forecast Input'!$A$2:$A$136=$B$4)*('Forecast
Input'!$B$2:$B$136=$A$15),'Forecast Input'!J$2:j$136)

now you can easily add something to this formula:
=SUMPRODUCT(('Forecast Input'!$A$2:$A$136=$B$4)*('Forecast
Input'!$B$2:$B$136=$A$15),'Forecast Input'!J$2:j$136) + W14 + (V27/36)
 
I assume you eman you want to add +W14+(v27/36) to the end of the formula, in
which case just do so but make sure you array enter the formula using
CTRL+SHIFT+ENTER.
 
Back
Top