SUMIFS with a dynamic, offset criteria range

[adelcap]

Hi everyone,

This one has me stumped. I need to sum numbers in one column that correspond to variable ranges in another column. I've considered using SUMIFS with an OFFSET and MATCH but I'm not having much luck.

On the attached worksheet:

In the 'Summary Sheet' tab, I need to sum the following in the 'Data Sheet' tab:

Sum the "Local Amount", in Column G, that corresponds to the currency e.g. British Pounds (in Column A), on Date 21/07/2014 (also in Column A), with 'Purchase' in Column D. The report in the 'Data Sheet' tab is an automated report and the rows in Column A change each time the report is run, depending on how many currencies there are and how many trades there are. Currencies are always separated in Column A by first the currency name (e.g. British Pounds) and then the currency name total (e.g. British Pounds Total).

Very grateful if anyone can help!

Thanks,