Board index » Excel » smaller formula to sum together 4 columns, 3 of them next to one a

Excel30
this is what i have now:



=INDEX(EBITDA!$L$13:$L$6000,MATCH(D13,EBITDA!$D$13:$D$6000,0),1)+INDEX(EBITDA!$M$13:$M$6000,MATCH(D13,EBITDA!$D$13:$D$6000,0),1)+INDEX(EBITDA!$N$13:$N$6000,MATCH(D13,EBITDA!$D$13:$D$6000,0),1)+INDEX(EBITDA!$O$13:$O$6000,MATCH(D13,EBITDA!$D$13:$D$6000,0),1)



you see this formula is on one worksheet, matching numbers on another

worksheet to sume together 4 columns. This looks ugly. Is there a smaller

way of doing this -- to at least add together columns m, n and o all at once?



thanks for taking a look...


-

Hello Steve,



with an "array formula" that needs to be coonfirmed with CTRL+SHIFT+ENTER



=SUM(VLOOKUP(D13,ebitda!$D$13:$O$6000,{9,10,11,12},0))



or if you know that D13 has only one match in ebitda!$D$13:$D$6000 you could

use



=SUMPRODUCT((ebitda!$D$13:$D$6000=D13)*ebitda!$L$13:$O$6000)



"SteveC" wrote:

or perhaps this is better.....



=SUM(OFFSET(ebitda!$L$13:$O$13,MATCH(D13,ebitda!$D$13:$D$6000,0)-1,))



"daddylonglegs" wrote:

Or even



=SUMPRODUCT((EBITDA!$D$13:$D$6000=D13)*(EBITDA!$L$13:$O$6000))





--



Regards,



Peo Sjoblom









"daddylonglegs" <d.addylonglegs@virgin.net>wrote in message



-