Counting Multiple answers with multiple criterias

[Joefried]

I need a formula of:
If the following options are met:
1) 'sheet2'A:A matches 'sheet1'A21
2) 'sheet2'D:D matches 'sheet1 F20

Now when he finds them I will need the answer for the sum of the amounts found in 'sheet2'E:E and if there are two matches give me a sum of both together

('Sheet2'E:E) are charges meaning they are $0.00

 

[jbeaucaire]

In Excel 2003 you'll have to decide how many rows you want to check, lets do 500 rows....

=SUMPRODUCT(--(Sheet2!$A$1:$A$500=Sheet1!A21),--(Sheet2!$D$1:$D$500=Sheet1!F20), Sheet2!$E$1:$E$500)


In Excel 2007+ you can use SUMIFS().

=SUMIFS(Sheet2!$E:$E, Sheet2!$A:$A, Sheet1!A21, Sheet2!$D:$D, Sheet1!F20)