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

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

The rate is needed separately. Concatenation would thwart that need.

 

[mikewray]

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

Ok, I am the first to admit that I am being an idiot here and it is with some embarassement that I write this. I got help a little while ago on this question

I have used it plenty and modified it sum for various applications. However, one modification I cannot seem to get (and I have sweated in vein) --> if a date column is created in the data table (say column A) and the corresponding values shown in a single column (say column D which is Feb at the moment), how do I get the same result as the last formula. Aladin are you there? Somebody please help and put me out my misery.

 

[Zack Barresse]

what formula are you using? did you edit the last one Aladin gave you at all?

 

[mikewray]

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

For simplicity, use the one Aladin did as a basis, it works...

=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))))

Only difference I want is instead of the dates being horizontal in row 11, I want the dates in a single column (say column A) and the corresponding values in a single column (say column D).

Tx for the quick response...

 

[Aladin Akyurek]

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

...I got help a little while ago on this question

I have used it plenty and modified it sum for various applications. However, one modification I cannot seem to get (and I have sweated in vein) --> if a date column is created in the data table (say column A) and the corresponding values shown in a single column (say column D which is Feb at the moment), how do I get the same result as the last formula...

Did you change the lay-out to which you want to adapt the formula or is it something else?

 

[mikewray]

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

Yes, the data sheet in real life example has changed. We getting a ledger feed which has the date in a column (not a row) and the corresponding value in another single column - but basically the same information. Just re-arranged. Of course there will now be repeated dates (whereas in the last each date was unique).

Do you want me to up the example up reflecting this...or will the previous suffice?

 

[Aladin Akyurek]

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

Yes, the data sheet in real life example has changed. We getting a ledger feed which has the date in a column (not a row) and the corresponding value in another single column - but basically the same information. Just re-arranged. Of course there will now be repeated dates (whereas in the last each date was unique).

Do you want me to up the example up reflecting this...or will the previous suffice?

Would be easier to have the example in the new layout?

 

[mikewray]

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

will do tomorrow first thing...I got to go to bed....

thanks...

 

[mikewray]

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

Right, I have made it like the real thing (may as well). The problem is as follows and what I am after is defined (with some notes below).

Mr excel board question.xls
	A	B	C	D	E	F	G	H
1	DATA
2	Acct	Date	Cmpy	Category	Curr	Description	Amount	Assign
3	10	29-Feb-04	BAL	Cash	GBP	Openingbal29/2/04	18,873	BS
4	10	15-Mar-04	BAL	Cash	GBP	PymtOMCinvoices	(332)	IS
5	10	31-Mar-04	BAL	Cash	GBP	Bankcharges	(22)	IS
6	20	15-Mar-04	BAL	Cash	USD	PymtLARinvoice021/2004	(8,382)	IS
7	20	31-Mar-04	BAL	Cash	USD	Interest1Q04	119	IS
8	30	24-Feb-04	HFC	Equity	GBP	Openingbal29/2/04	34,748	BS
9	30	25-Feb-04	HFC	Equity	GBP	Dividends	332	IS
10	30	29-Feb-04	HFC	Equity	GBP	Purchases	(2,220)	BS
11	40	29-Feb-04	BAL	Cash	USD	Openingbal29/2/04	18,873	BS
12
13	XchangeratestoEUR
14	Period	Curr	Rate
15	29-Feb-04	GBP	1.5200
16	29-Feb-04	USD	0.8004
17	31-Mar-04	GBP	1.4900
18	31-Mar-04	USD	0.8200
19
20			REQUIREMENTS
21			Required1			Required2
22			Enterdate	31-Mar-04		Enterdate	29-Feb-04
23			EnterCategory	Cash		EnterCategory	Equity
24			BalinEUR	?		ISmvmt(EUR)formonth	?
25			Correctans	36,292		Correctans	505
Sheet1

Notes:
You get a closing balance by adding all the transactions up to (and including) the relevant date,

Note transaction dates dont necessarily fall at month end (I could modify the source data but that would take time).

Requirement 1: Closing Balance in EUR of each category i.e. calculated closing balance of category (sum of transactions to date) x report date (C22) xchange rate (for each currency)

Requirement 2: IS (income statement) movement for the month on a category in EUR i.e. sum of transactions on a category x month end xchange rate

I have put in control answers (I calculated manually) so you have a guide.

Would like a single formula for each requirement without edits to the data or more columns but if these are required then c'est la vie, would like them minimised.

I really want to understand this so I can use it myself and not waste your time. I thought I had it worked out but clearly I dont. Sorry...but thank you.

 

[Aladin Akyurek]

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

...Requirement 1: Closing Balance in EUR of each category i.e. calculated closing balance of category (sum of transactions to date) x report date (C22) xchange rate (for each currency)

I get: 36293.51

Requirement 2: IS (income statement) movement for the month on a category in EUR i.e. sum of transactions on a category x month end xchange rate

Does this require a different formula from the one for Requirement 1? If not, is your control figure correct?