Friday, July 23, 2010

Excel - Sumproduct Function

Multiplies corresponding components in the given arrays, and returns the sum of those products.
SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... are 2 to 255 arrays whose components you want to multiply and then add.

I know you may only get a vague idea about using this tool from the above explanation.
See the example in the screen shot below.
In the sample data above for some particular job (procurement/fabrication/erection of structural steel) (column E) has assigned some % wtg (Column F).

If the job done totally the % wtg. will be 100. The % Wtg. is only for calculating the job progress.

The qty. of the job is shown in Column G and the job executed in Column H.
How can we find total the % Wtg. achieved?
In column I you can see the % achieved against each activities (Column H/Column G* Column F) and totaled it to get the total % Wtg. achieved (I8).

See the screen shot below.
If we use SUMPRODUCT function we can avoid Column I, i.e. we can directly get the output by applying formula in cell H8 or any cell other than the data entered.
Get Free Updates:
We will send a confirmation mail. Please check your inbox/spam folder to confirm it
Related Posts Plugin for WordPress, Blogger...

Copyright © 2011 All Rights Reserved | Designed by Amith | Post RSS | Home | About | Disclaimer | Privacy Policy | Contact