Matching a value in a column & then summing a respective cell from a the matched value..

Hi....

I am trying to sum the total amount of Components required to be changed.

I have 'Sheet 2' which lists each of the components, per 'Sub Component'.

'Sheet 1' is meant to sum all of the sub components according to their 'Component Name'

For example,

On 'Sheet 1' in Cell E6 I wish to enter a formula search for the value of B3 (Pump) on Sheet two in 'Column C'. I wish this formula to return the Sum of the numbers in Column E whenever the adjacent cell in Column C matches that of Cell B3 from Sheet 1.

For further understanding... with the current data on the Excel file attached the Formula of

Cell E6 should return the value of '3'.
Cell E7 should return the value of '6'.
Cell E8 should return the value of '9'.
Cell E9 should return the value of '12'.


Is this possible??

Test Online.xlsx

Originally Posted by icestationzbra

due the way your tableau in Sheet2 is laid out (gaps, merged cells etc.), this formula is not very robust:

Please Login or Register  to view this content.

enter in E6 on Sheet1 and drag-fill down until E9. then, copy formula to E15, adjust ranges, drag-fill down to E18.

I got it!!

I had to first formulate the 'Jan' 'Feb' 'Mar' Cells etc into a month format 'mmmm'

then

I could use the SUMPRODUCT formula to guarantee two criteria are met & it now sums the totals for the entire columns when the two criteria are met...

Thanks for the reply anyways..