To return the sum of cells for postive matches by using index and match functions

Hi

Attached is a file that using index and match functions in column E. May i know how to add up all the figure that have the same ID? Please be advised that sumif function is not an appropriate function to use in my case as i need to lookup the value in my actual worksheets.

Please look into wks Source, 1173 & 1174. It can be seen that 1173 did not pull all the relevant data from source. Is there an alternative?

Thanks

Originally Posted by mingali

Please be advised that sumif function is not an appropriate function to use in my case as i need to lookup the value in my actual worksheets.

The above does not really make sense ... can you clarify ?

SUMIF is the correct function to use UNLESS the source data is stored in an external file which is not open (in which case use SUMPRODUCT)

If you added SubTotals to your Pivot (oldAccount) you could then just use GETPIVOTDATA function.

If you want to use what you have - SUMIF is still the correct function.

=SUMIF(source!A:A,$A1,source!C:C)

You would simply adjust the sum_range to reflect the correct column (per sheet name)

You can automate the above based on the sheet name (and applying a MATCH within an INDEX).

However, the function used to retrieve the sheet name is Volatile meaning all dependants thereof become Volatile by association.

If you opt for this common route I would suggest storing the sheet name manually in a common cell - eg E1
(avoiding use of CELL function to automate sheet name given point above).

In F1 use a MATCH (to avoid repetitive calculations)

F1: =MATCH($E1,Source!$5:$5,0)

At which point the SUMIF can be common to all sheets

=SUMIF(Source!$A:$A,$A1,INDEX(Source!$A:$Z,0,$F$1))
copied down
(modify ranges to suit)

All you need do is adjust the value in E1 on each sheet for the calcs to update
(note the values would be text '1173 rather than number 1173 based on your sample file)

The SUMPRODUCT equivalent of:

=SUMIF(source!A:A,$A1,source!C:C)

would be

=SUMPRODUCT(--('C:\folder\[filename.xls]sheetname'!criteriarange=$A1),'C:\folder\[filename.xls]sheetname'!rangetosum)

When using SUMPRODUCT it is imperative you keep range references as lean as possible given the iterative nature of the function
pre XL2007 you can not use entire column references (A:A) though even though viable in XL2007 such references should be avoided at all costs