Index/match/Search/product sum?

[Kangah]

Hi guys, not quite sure where to begin and I have tried a number of ways but I just don't know what will work and have already lost hours and I don't think I am any closer!

Hopefully someone can at least tell me what sort of formula/combination I will need.

Essentially what I need to do is populate the "table" at the bottom (D12:F15) with the numbers (in cells C2:N10) based on the Portfolio code, matches the Month (In Cell K12) and the "PRODUCT" Type (Column O).

So for example, Cell E13 should be $72132.2615 because it is the cell that is...

- Portfolio HGF01
- Month is "SEPTEMBER"
- Product is "TOTAL DATA

Any idea of what formula would work or where to begin?

Thanks in advance.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	JULY	AUGUST	SEPTEMBER	OCTOBER	NOVEMBER	DECEMBER	JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	PRODUCT	Portfolio
2	50492.5831	57705.8092	72132.2615	68525.6484	60230.44	57705.81	32459.52	46885.97	64919.04	62394.41	72132.26	75738.87	Total Data	HGF01
3	50492.5831	57705.8092	72132.2615	68525.6484	60230.44	57705.81	32459.52	46885.97	64919.04	62394.41	72132.26	75738.87	Total Data	HGF02
4	50492.5831	57705.8092	72132.2615	68525.6484	60230.44	57705.81	32459.52	46885.97	64919.04	62394.41	72132.26	75738.87	Total Data	HGF03
5	50492.5831	57705.8092	72132.2615	68525.6484	60230.44	57705.81	32459.52	46885.97	64919.04	62394.41	72132.26	75738.87	Total Other	HGF01
6	50492.5831	57705.8092	72132.2615	68525.6484	60230.44	57705.81	32459.52	46885.97	64919.04	62394.41	72132.26	75738.87	Total Other	HGF02
7	23752.9729	27146.2547	33932.8184	32236.1775	28333.9	27146.25	15269.77	22056.33	30539.54	29351.89	33932.82	35629.46	Total Other	HGF03
8	23752.9729	27146.2547	33932.8184	32236.1775	28333.9	27146.25	15269.77	22056.33	30539.54	29351.89	33932.82	35629.46	Total Next	HGF01
9	23752.9729	27146.2547	33932.8184	32236.1775	28333.9	27146.25	15269.77	22056.33	30539.54	29351.89	33932.82	35629.46	Total Next	HGF02
10	9625.5319	11000.6079	13750.7599	13063.2219	11481.88	11000.61	6187.842	8937.994	12375.68	11894.41	13750.76	14438.3	Total Next	HGF03
11
12		Portfolio	Total Data	Total Other	Total Next			Month	SEPTEMBER
13		HGF01
14		HGF02
15		HGF03
Sheet1

 

[rjb2k]

Hi I have a question further to this post the answer to which I think I am close to having read this post but need some clarification.

I have a spreadsheet to track sales and product data. For example the cost and sales price of products and in addition a very simple sales book showing who has ordered what.

This is very new and was fine for a month or so however now there are slight changes occurring to our own prices and also our cost price. I therefore need to be able to update the line items cost and sales price without it effecting the previous sales data entries.

Rather than change the sales data, making it static etc I plumbed for creating new line items with the adjusted figures.

http://dl.dropbox.com/u/15969315/Capture.PNG

I wasn't sure if you are able to upload screen grabs so you can see through my public dropbox.

As an example:
date product cost sale
1st Sep 11, product 1, £10, £20
21st Sep 11, product 1, £12, £22

These updates will be fairly infrequent so adding new line items will not become a problem but keeping track of date sensitive (due to price changes) sales data is important.

I want sales data to look at the date before the lookup of price? So if I have sold one of product 1 on 3rd Sept it will reflect the older data and then another sale of product 1 on 1st October will reflect the new data.

I was looking at sumproduct, index, match and offset functionality but cannot seem to put them together in the right order.

Thanks for your help... Apologies if this is not quite in the right thread.