FORMULA TO ADD AMOUNT BASED ON TWO or MORE CONDITIONS

HI,
i need a formula to add amount based on two or more conditons.
From the drop down list on each change i want to do the sum to be displayed in C18;
see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas.

Also tell me any other array formulas like this which are useful.

The formula you want in C18 is:

=SUMPRODUCT(--($B$2:$B$11=C15),--($C$2:$C$11=C16),--($D$2:$D$11=C17),E2:E11)

Also, you can greatly simplify the Data Validation for the Date cell (C15) by changing the validation from LIST to DATE.

Complete explanation of SUMPRODUCT:
http://www.xldynamic.com/source/xld....T.html#classic

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].



(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)

[sarcastic mode = ON]

Please Login or Register  to view this content.

[sarcastic mode = OFF]

(wink-wink-nudge-nudge)