If you set the B-column to be the exchange rate, you can use the sumproduct formula like this, with the following data set:
A1=$10, A2=€10, B1=1.24, B2=1
=SUMPRODUCT(A1:A2;B1:B2)
As I was building this question, I figured out several ways to achieve this, so I went ahead and shared the information.
There are several ways to do this. The first is a variation on your original syntax, but using nested IF statements instead of IF and AND:
=ARRAYFORMULA(SUM(IF(MONTH($A$1:$A$5)=MONTH(E1), IF(YEAR($A$1:$A$5)=YEAR(E1), $B$1:$B$5))))
The second uses the FILTER function. This method will return a #N/A error if FILTER doesn't find any matches for the conditions. FILTER takes each condition as a separate argument:
=SUM(FILTER($B$1:$B$5, MONTH($A$1:$A$5)=MONTH(E1), YEAR($A$1:$A$5)=YEAR(E1)))
The third uses INDEX and SUMPRODUCT:
=INDEX(SUMPRODUCT((MONTH($A$2:$A$6)=MONTH(E2))*(YEAR($A$2:$A$6)=YEAR(E2))*$B$2:$B$6), 1)
In each of these examples, I assumed that the data were in columns A and B, the "pivot table" dates were in column E, and the aggregated data are placed in column F.
There might be a way to do this with the QUERY function that provides an interface to the Google Visualization API Query Language, but I'm not sure. I don't know if such a query would dynamically update, either.
Best Answer
Since you want to return the sum of each of 3 columns you could use the following query formula instead
The above formula will give you results for just the month of February.
You can alter the formula to get results for each month
Functions used:
QUERY