I would like to use Bob's array formula as below,

If(x1="PGR",SUM(N(OFFSET(INDEX(Sheet1!$C$12:$H$16, 0,MATCH(MIN($B2,3)&$C2,{"1E","2E","3Â*E","1O","2O" ,"3O"},0)),MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2 ))),1),Sheet1Â*!$B$12:$B$16,0)-1,0))),"")

The first condition returning the array formula.




"Pete_UK" wrote:

My column headings were the opposite way round - E1, E2, E3, O1, O2,
O3, where E3 and O3 meant greater than 2 years.

I'm not sure what IF you want to incorporate within this array formula
from Bob.

Pete

On Apr 23, 2:46 pm, nc wrote:
Peter

I am trying to use this array formula

=SUM(N(OFFSET(INDEX(Sheet1!$C$12:$H$16,0,MATCH(MIN ($B2,3)&$C2,{"1E","2E","3Â*E","1O","2O","3O"},0)), MATCH(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),Shee t1Â*!$B$12:$B$16,0)-1,0)))

I have tried your solution but does not seem to work.



"Pete_UK" wrote:
Depends what you want to do, but here's one approach:

=IF(X1="",0,array_formula)

commit with CTRL-SHIFT-ENTER.

However, if you want an alternative non-array solution to your earlier
post, check out my answer.

Hope this helps.

Pete

On Apr 23, 2:14 pm, nc wrote:
How can I include an array function within an IF function?

Thanks.- Hide quoted text -

- Show quoted text -