Index & Match - Doesn't return sum of multiple instances

Excel 2003

Using index & match to find and return sum. however, the formula does not return the sum of all instances, only seems to pick up first entry.
Thanks for your help, I appreciate it.

I changed your formula from using the index and match, which only brings over the first occurrence, to using a SUM Array formula: =SUM(($A$2:$A$8=A13)*($B$2:$B$8=B13)*$G$2:$G$8). Here is your workbook modified as such.

This may work, but what happens for cells that may have a zero value. Will this return an error message?

It should result in a zero if it cannot find a match. You could always do this =IF(ISERROR(SUM(($A$2:$A$8=A13)*($B$2:$B$8=B13)*$G$2:$G$8)),0,SUM(($A$2:$A$8=A13)*($B$2:$B$8=B13)*$G$2:$G$8)).

Thanks - I'll give it a try

Originally Posted by limits7

It should result in a zero if it cannot find a match. You could always do this =IF(ISERROR(SUM(($A$2:$A$8=A13)*($B$2:$B$8=B13)*$G$2:$G$8)),0,SUM(($A$2:$A$8=A13)*($B$2:$B$8=B13)*$G$2:$G$8)).

Does the information have to be on the same page for this to work? I tried this pulling data from different workbooks and the results are #Value. However, if I put the data on the same page as I did in my Excel example, it works fine.

Originally Posted by limits7

It should result in a zero if it cannot find a match. You could always do this =IF(ISERROR(SUM(($A$2:$A$8=A13)*($B$2:$B$8=B13)*$G$2:$G$8)),0,SUM(($A$2:$A$8=A13)*($B$2:$B$8=B13)*$G$2:$G$8)).

It can be on another sheet. You will have to change the following ranges: $A$2:$A$8 will have to be the range that contains the Project Code, $B$2:$B$8 will have to be the range that Activity Code, & $G$2:$G$8 will have to be the range that contains Total. For example if they where all on a TAB labeled "Billing" the ranges would be, respectively,: Billing!$A$2:$A$8, Billing!$B$2:$B$8, & Billing!$G$2:$G$8. See attachment