Sum two column criteria

[mike1984]

Hi all,


I have a database which has order numbers as rows and departments as columns, with the value of items as the data.


I am looking to sum up the value of items where two particular departments were shopped in the same transaction.


I have attached an example of the data (left hand matrix) and the result I would like (right hand matrix).


Hopefully the spreadsheet will help to explain better than I can! :)


Thanks


Mike

[p45cal]

In your attached file, in cell AG2:
=SUMPRODUCT((OFFSET($A$2:$A$29,0,MATCH(AG$1,$B$1:$AD$1,0))>0)*(OFFSET($A$2:$A$29,0,MATCH($AF2,$B$1:$AD$1,0))>0),(OFFSET($A$2:$A$29,0,MATCH(AG$1,$B$1:$AD$1,0)))+(OFFSET($A$2:$A$29,0,MAT CH($AF2,$B$1:$AD$1,0))))
copied across and down.

You will only need a triangle of the data (top right or bottom left of the resultant grid, and you won't even need the formulae on the diagonal as they refer to the same dept. So you can delete AG2 itself after having copied it across the grid.

Your 49.96 example sits in cell AM3 and AH8.