Help: Array formula components work separately but not together

[indigoant]

I have an array formula that essentially needs to sum a set of values in one range when cells in another range contain text.

To do this i'm using an array formula which compares two ranges. One range contains text cells the other values. Whenever a cell in the first range contains text I want to sum the values of the second range in the corresponding row. If I enter the ranges specifically the formula works. However I want to make the formula generic so that it deals with new rows within the work sheet and I can copy it to additional columns. To this end I was using ROW() and COLUMN() functions within the ADDRESS() function.

This is where I hit the problem. The formula no longer works stating a value has the wrong data type.

=SUM(ISTEXT(INDIRECT(ADDRESS(ROW()+1,4)&":"&ADDRESS(G5,4)))*INDIRECT(ADDRESS(ROW()+1,COLUMN())&":"&ADDRESS(G5,COLUMN())))

G5 contains the max row number for the table.

Any help would be gratefully received. I've attached a snippet of the full worksheet to show the problem.

[Lotus123]

Your formula isn't working because ROW() and COLUMN() do not work as you are expecting in an array formula.

Note if I take those out and use this formula:

{=SUM(ISTEXT(INDIRECT(ADDRESS(5+1,4)&":"&ADDRESS(G5,4)))*INDIRECT(ADDRESS(5+1,8)&":"&ADDRESS(G5,8)))}

....it works fine. The answer is 200.

Alternative to the ROW and COLUMN you could dynamicly name your ranges and then use those names in the formula. For example, I defined ='2008'!D6:D101 as LEFTRANGE and I also defined ='2008'!H6:H101 as RIGHTRANGE. If I insert a new row between row 6 and row 101...the named ranges will dynamicly adjust to 6:107...and 6:108, etc. If you don't trust the dynamics here (because it can fail if you insert a row after 101...the defined name won't update then), you can use something similar to how you calculated the last row in the column to define the named ranges.

I then can simply use this formula:

{=SUM(ISTEXT(leftrange)*rightrange)}

It also results in an answer of 200.

Hope the above works for you!

[daddylonglegs]

Try

=SUMIF(OFFSET(D5,1,,G5-ROW()+1),"<>",OFFSET(H5,1,,G5-ROW()+1))

[indigoant]

Huge thanks guys I can sleep again!

Both far more elegant solutions than my clunky efforts. Went for the slick offset method in the end as it met my needs best.

Thanks again.
Cheers!