Excel 2007 : extract value using If; plus convert value to different Rates

hi, im not sure if it is possible to use Sumif, to extract the value from cell using multiple criteria,
and also convert different Currency into one standard exchange rate


I hope this xls will give you an clearer picture of what i mean....

any advice and solution welcome!
thanks in Advance
regards
GZ

Down and dirty this is really 3 formulas in one cell. Put this in B3 and then copy down and across:

=SUMPRODUCT(--('Offer Sheet'!$A$2:$A$46=2007),--('Offer Sheet'!$C$2:$C$46=$A3),--('Offer Sheet'!$D$2:$D$46=B$2),--('Offer Sheet'!$E$2:$E$46="EUR"), 'Offer Sheet'!$F$2:$F$46) + SUMPRODUCT(--('Offer Sheet'!$A$2:$A$46=2007),--('Offer Sheet'!$C$2:$C$46=$A3),--('Offer Sheet'!$D$2:$D$46=B$2),--('Offer Sheet'!$E$2:$E$46="SGD"), 'Offer Sheet'!$F$2:$F$46) *0.5 + SUMPRODUCT(--('Offer Sheet'!$A$2:$A$46=2007),--('Offer Sheet'!$C$2:$C$46=$A3),--('Offer Sheet'!$D$2:$D$46=B$2),--('Offer Sheet'!$E$2:$E$46="USD"), 'Offer Sheet'!$F$2:$F$46) * 0.7


Some of your column D values on the Offer Sheet need correcting...they should be FEB.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

There is no need for 3 SUMPRODUCTs here - persist with SUMIFS as it is far more efficient (esp. when used in triplicate)

Please Login or Register  to view this content.