PDA

View Full Version : Solved: Irregular Sum

f2e4
01-19-2011, 02:53 AM
See attached sheet.

I am trying to do a SUM function to simply work out annual totals per project.

However, not all projects ended in December, therefore, just doing a SUM of the December column is giving an false total.

Can anyone have a look at the attached sheet and see if there is some cunning formula I can use to fix the error.

Note: The values in cells D3:O24 are cumulative values (i.e. the last populated column for each row is the overall total for that project)

Thanks again for any help

p45cal
01-19-2011, 12:19 PM
will this do?:
R3:
=IFERROR(INDEX(D3:O3,MATCH(9E+99,D3:O3,1)),0)
copied down to R24
Then sum those values.

If using pre xl2007, R3 should be:
=IF(ISERROR(INDEX(D3:O3,MATCH(9E+99,D3:O3,1))),0,INDEX(D3:O3,MATCH(9E+99,D3 :O3,1)))

f2e4
01-20-2011, 02:29 AM
will this do?:
R3:
=IFERROR(INDEX(D3:O3,MATCH(9E+99,D3:O3,1)),0)
copied down to R24
Then sum those values.

If using pre xl2007, R3 should be:
=IF(ISERROR(INDEX(D3:O3,MATCH(9E+99,D3:O3,1))),0,INDEX(D3:O3,MATCH(9E+99,D3 :O3,1)))

Works perfectly p45cal, thanks very much!!