SUMIF, VLOOKUP (for currency conversion on various months)

[mikewray]

Frustrated and cannot get this, no matter which way I try. What formula will give me the EUR amounts for any given month (from cell B3)? See table below...

I dont want to create any more columns etc, I know the long way round...I am looking for an elegant solution. Is sumif, vlookup possible to combine or do I have to use sumif, index, match which I dont really understand. Problem illustrated below, first part is what I want and below is the source data (balances per month in base currency and xchange rate table):

CFFC 0403 v1.xls
	A	B	C	D	E
1	ReportReqd
2
3	Formonth	29-Feb-04
4
5	Category	EUR
6	Bank	formula?
7	Property	formula?
8	Funds	formula?
9
10	Data
11	Company	Category	Currency	29-Feb-04	31-Mar-04
12	FPG	Bank	GBP	18,873	10,255
13	CIO	Bank	GBP	34,748	16,348
14	YTR	Bank	USD	2,654	(6,029)
15	FPG	Bank	GBP	2,232	7,033
16	CIO	Bank	EUR	47,286	18,886
17	YTR	Bank	GBP	13,751	14,237
18	FPG	Property	USD	350,000	350,000
19	FPG	Funds	EUR	29,435	31,234
20	FPG	Property	USD	98,858	102,345
21	YTR	Funds	GBP	120,293	138,907
22
23	XCRates
24	Date	Curr	Rate
25	29-Feb-04	EUR	1.0000
26	29-Feb-04	GBP	0.6686
27	29-Feb-04	USD	1.2493
28	31-Mar-04	EUR	1.0000
29	31-Mar-04	GBP	0.6814
30	31-Mar-04	USD	1.3124
for mr excel

 

[Aladin Akyurek]

Re: SUMIF, VLOOKUP (for currency conversion on various month

In B6 enter & copy down:

=SUMPRODUCT(--($B$12:$B$21=A6),$D$12:$D$21,LOOKUP($C$12:$C$21,$B$25:$C$27))

Note that A25:C27 must be sorted in ascending order on Curr column. Same for A28:C30. The above formula does not select itself whic one of these areas (that is, B25:C27 or B28:C30) to use.

 

[indiantrix]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Although I had to add an extra column (Concat), This seems to accommodate the several date/currency combinations in the lower chart. Hope this is of some help... /s/ Larry

Book2
	A	B	C	D	E
1	ReportReqd
2
3	Formonth	29-Feb-04
4
5	Category	EUR	GBP	USD
6	Bank	119544	79927	149346
7	Property	448858	300106	560758
8	Funds	149728	100108	187055
9
10	Data
11	Company	Category	Currency	29-Feb-04	31-Mar-04
12	FPG	Bank	GBP	18,873	10,255
13	CIO	Bank	GBP	34,748	16,348
14	YTR	Bank	USD	2,654	-6,029
15	FPG	Bank	GBP	2,232	7,033
16	CIO	Bank	EUR	47,286	18,886
17	YTR	Bank	GBP	13,751	14,237
18	FPG	Property	USD	350,000	350,000
19	FPG	Funds	EUR	29,435	31,234
20	FPG	Property	USD	98,858	102,345
21	YTR	Funds	GBP	120,293	138,907
22
23	XCRates
24	Date	Curr	Concat	Rate
25	29-Feb-04	EUR	38046EUR	1
26	29-Feb-04	GBP	38046GBP	0.6686
27	29-Feb-04	USD	38046USD	1.2493
28	31-Mar-04	EUR	38077EUR	1
29	31-Mar-04	GBP	38077GBP	0.6814
30	31-Mar-04	USD	38077USD	1.3124
Sheet1

 

[mikewray]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Tx, for the quick response Aldin and indiantrix. Aldin, your ingenius formula works and nearly does what I want, but there is an additional requirement which it omits...

What I would like is for the EUR balances to be calculated for the date entered in cell B3. So if 31-3-04 is entered in B3 the EUR balances for 31-3-04 are reflected and if 29-2-04 is entered it returns the values that your existing formula does. Is this possible? Reason why I ask is that I could be analysing any one month...

PS for my own education please explain the '--' in the formula you gave...the rest I can figure out.

indiantrix - your formula does not work, for example Property for 29/2/04 should be (350000+98858)*1.2493=EUR560758 (as Aldin formula calculates). Looks like it is just getting the absolute amount and not multiplying by the applicable exchange rate.

Many tx...

 

[Aladin Akyurek]

Re: SUMIF, VLOOKUP (for currency conversion on various month

...What I would like is for the EUR balances to be calculated for the date entered in cell B3. So if 31-3-04 is entered in B3 the EUR balances for 31-3-04 are reflected and if 29-2-04 is entered it returns the values that your existing formula does. Is this possible? Reason why I ask is that I could be analysing any one month...

You apparently missed the qualification I included in my original post:

"The above formula does not select itself whic one of these areas (that is, B25:C27 or B28:C30) to use."

But, as you wish...

=SUMPRODUCT(--($B$12:$B$21=$A6),INDEX($D$12:$E$21,0,MATCH(B$3,$D$11:$E$11,0)),LOOKUP($C$12:$C$21,INDEX($B$25:$B$30,MATCH($B$3,$A$25:$A$30,0)):INDEX($C$25:$C$30,MATCH($B$3,$A$25:$A$30,1))))

PS for my own education please explain the '--' in the formula you gave...the rest I can figure out...

It converts the logical values (TRUE and FALSE) into Excel's numeric equivalents 1 and 0, respectively. This "coercion" as it's called is required because SumProduct like most of functions requires numbers.

 

[mikewray]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Yes, that works! But the actual spreadsheet I am working on has has 12 months across (Feb, Mar, Apr, May etc). Your formula (I knew it would have to have the index match which I still dont understand) only caters for Feb and March. I dont mind putting in more columns in the XRate table if necessary, just dont want more columns or rows anywhere else.

Tx you once again, we are nearly there I think...I really appreciate it.

 

[Aladin Akyurek]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Yes, that works! But the actual spreadsheet I am working on has has 12 months across (Feb, Mar, Apr, May etc). Your formula (I knew it would have to have the index match which I still dont understand) only caters for Feb and March. I dont mind putting in more columns in the XRate table if necessary, just dont want more columns or rows anywhere else.
...

we are nearly there I think...

Not "we", you're nearly there...

Are you trying to tell me that you'll have 12 different dates in row 3 and corresponding data below so that the formula should be copiable across?

 

[mikewray]

Re: SUMIF, VLOOKUP (for currency conversion on various month

not to worry, figured it out, just needed to modify the references in your formula to include a larger range...all working sweetly. Not sure I understand it all (the index match bit gets me, any tips?) but will endeavour too as I think I will have applications for this else where. Many thanks....

 

[Aladin Akyurek]

Re: SUMIF, VLOOKUP (for currency conversion on various month

not to worry, figured it out, just needed to modify the references in your formula to include a larger range...all working sweetly. Not sure I understand it all (the index match bit gets me, any tips?) but will endeavour too as I think I will have applications for this else where. Many thanks....

In:

=SUMPRODUCT(--($B$12:$B$21=$A6),INDEX($D$12:$E$21,0,MATCH(B$3,$D$11:$E$11,0)),LOOKUP($C$12:$C$21,INDEX($B$25:$B$30,MATCH(B$3,$A$25:$A$30,0)):INDEX($C$25:$C$30,MATCH(B$3,$A$25:$A$30,1))))

the INDEX($D$12:$E$21,0,MATCH(B$3,$D$11:$E$11,0)) bit determines which subrange to pick out from D12:E21... All cells of the column computed by the MATCH bit from D12:E21. The MATCH bit returns here 1 or 2, depending on the date in B$3.

The LOOKUP($C$12:$C$21,INDEX($B$25:$B$30,MATCH(B$3,$A$25:$A$30,0)):INDEX($C$25:$C$30,MATCH(B$3,$A$25:$A$30,1))) bit looks up all values of C12:C21 in the subrange INDEX(...):INDEX(...). The first MATCH bit here determines where the subrange starts and the 2nd MATCH where the subrange ends.

 

[mikewray]

Re: SUMIF, VLOOKUP (for currency conversion on various month

Yes, I think I get it. The lookup bit could be simplified (?) if I created a key in the exchange rate table that concatanates the currency and the rate.

Right, bonne. Merci beaucoup!