excel - Array formula to sum the value of each element -


this formula have apply cell in several rows in excel sheet:

(this 1 row 20, there 200+ rows)

=(     z20*z$11*z$9*vlookup(v20,r5:t8,3,false)*x20+     aa20*aa$11*aa$9*vlookup(v20,r5:t8,3,false)*x20+     ab20*ab$11*ab$9*vlookup(v20,r5:t8,3,false)*x20+     ac20*ac$11*ac$9*vlookup(v20,r5:t8,3,false)*x20+     ad20*ad$11*ad$9*vlookup(v20,r5:t8,3,false)*x20+     ae20*ae$11*ae$9*vlookup(v20,r5:t8,3,false)*x20+     af20*af$11*af$9*vlookup(v20,r5:t8,3,false)*x20+     ag20*ag$11*ag$9*vlookup(v20,r5:t8,3,false)*x20+     ah20*ah$11*ah$9*vlookup(v20,r5:t8,3,false)*x20+     ai20*ai$11*ai$9*vlookup(v20,r5:t8,3,false)*x20+     aj20*aj$11*aj$9*vlookup(v20,r5:t8,3,false)*x20+     ak20*ak$11*ak$9*vlookup(v20,r5:t8,3,false)*x20+     al20*al$11*al$9*vlookup(v20,r5:t8,3,false)*x20+     am20*am$11*am$9*vlookup(v20,r5:t8,3,false)*x20+     an20*an$11*an$9*vlookup(v20,r5:t8,3,false)*x20+     ao20*ao$11*ao$9*vlookup(v20,r5:t8,3,false)*x20 )/1000 

i tried use array formula make bit neater, e.g.

=(z20:ao20*z$11:ao$11*z$9:ao$9*vlookup(v20,r5:t8,3,false)*x20)/1000 

however value comes out wrong, , think because value being multiplied @ each element rather added want.

(i'm aware might not doing think is!)

is there way make array formula sum element values?

(note: pivot table not possible here)

as happens, found answer after asked question:

=sum(z20:ao20*z$11:ao$11*z$9:ao$9*vlookup(v20,r5:t8,3,false)*x20)/1000 

Comments

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

Java 8 + Maven Javadoc plugin: Error fetching URL -

datatable - Matlab struct computations -