How can I write a Formula to do this.....

[alsexceladmin]

Formula teeth gnasher.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Spreadsheet			Range1
2	Ben	Site		IndividualSites	IS	JSA-6	NONE
3	IS	Crumpsall		Charlestown	18	14	14
4	JSA-6	Harpurhey		Crumpsall	18	14	14
5	NONE	Harpurhey		Harpurhey	18	14	14
6
7				Range2
8				LADVALUES	IS	JSA-6	NONE
9				Cheetham	6	2	2
10				Harpurhey	6	2	2
11				Bradford	6	2	2
12
13	Fromabovespreadsheet,howdoIcreateaformulatoproduceanumberedresultinagivencellforthefollowingexample:
14	Identifyindividualcellcontentof"A3"and"B3"(bothtext)respectively,thenmatchbothcriteriainRange"D2:G5"-
15	toproducethenumberresultofintersectingcells"E4"
16	Cellcontentin"A3","B3"comboappliedtoRange1sheetwouldgivearesultof18,similarlywith"A4","B4"combowouldgive14.
17	IalsoneedtoapplyasimilarformulatoRange2,thenaddthetworesultstogetherinagivencell.
Sheet1

 

[alsexceladmin]

Thanks anyway Ken!
Remember, that without your effort responding to people like myself....Where would we be! The first part works a treat now!!

I still have a small problem with the whole picture however. Going back to the beginning in this posting where my naff layout post stated that I needed the results from 2 seperate ranges - "added together"?
Well, I tried the formula for this which was kindly provided by Sean - but I just cannot get it to work (I just get "Value" in the cell)

Any ideas Gang!?!

alsexceladmin

 

[chiello]

... the formula for this which was kindly provided by Sean - but I just cannot get it to work (I just get "Value" in the cell)

I think the problem with Sean formula is in Input dimension. Infact all vectors (matrices) passed to the SUMPRODUCT function must have the same dimension.

For Range 1 with the formula =INDEX($E$2:$G$5,MATCH(B3,$D$3:$D$5,0),MATCH(A3,$E$2:$G$2,0)) you are looking for B3 in column label, A3 in row label.

Are you looking for B3 and A3 also in Range 2? Suppose Yes.

I propose the following solution (quite long but not complicated):

=IF(ISNA(INDEX($E$3:$G$5;MATCH(B3;$D$3:$D$5;0);MATCH(A3;$E$2:$G$2;0)));0;INDEX($E$3:$G$5;MATCH(B3;$D$3:$D$5;0);MATCH(A3;$E$2:$G$2;0)))+IF(ISNA(INDEX($E$9:$G$11;MATCH(B3;$D$9:$D$11;0);MATCH(A3;$E$8:$G$8;0)));0;INDEX($E$9:$G$11;MATCH(B3;$D$9:$D$11;0);MATCH(A3;$E$8:$G$8;0)))

Does it work ?

 

[alsexceladmin]

Having reset my spreadsheet, I am now looking for:See below

S/S(A1:B5) Range 1 (D1:G5) Range 2 (I1:L5)
A B "c" D E F G "h" I J K L
1 Ben Site Site IS IB NONE Site IS IB NONE
2 IB Chee Brad 18 18 14 Brad 6 6 2
3 IS Most Chee 12 8 6 Chee 6 6 2
4 NONE Harp Harp 18 18 14 Harp 6 6 2
5 JSA+6 Brad Most 20 18 14 Mile 6 6 2

Using the formula that works (as mentioned earlier) I obtain a result from matching A2 & B2 with Range 1 to produce - 8, A4 & B4 to produce 14 etc.

I need to obtain a result for these cells from the same row applied to both ranges and then added together.
A2 & B2 against Range 1 =(8) + A2 & B2 against Range 2 = (6) Total 14.
Ditto A4 & B4 - (14) + (2) Total 16

Hope this turns out ok, if it does I'm sure you will come up trumps for me again!

Thanking you in anticipation
alsexceladmin ray:

 

[chiello]

Hope this turns out ok, if it does I'm sure you will come up trumps for me again!
quote]

As you have foreseen, here are my solutions:

R2C.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Ben	Site		Site	IS	IB	NONE		Site	IS	IB	NONE		Italian	English
2	IB	Chee		Brad	18	18	14		Brad	6	6	2		SE	IF
3	IS	Most		Chee	12	8	6		Chee	6	6	2		VAL.NON.DISP	ISNA
4	NONE	Harp		Harp	18	18	14		Harp	6	6	2		INDICE	INDEX
5	JSA+6	Brad		Most	20	18	14		Mile	6	6	2		CONFRONTA	MATCH
6														;	,
7
8	Solution1		Solution2
9	14		14
10	#N/D		20
11	16		16
12	#N/D		0
13
Template

Ciao Ciao

 

[alsexceladmin]

Hi again chiello,
Because I still couldn't get it to work, I copied and pasted your (much better) example spreadsheet over. ( I cannot use HtmlMaker at work)
Once pasted over, I still couldn't get it to work. At first my pasting insisted on taking over an extra number column, so I pasted it over line by line.
I still cannot get it to work for me - it keeps telling me that there is an error in my formula. ( I have checked each of your entries including making sure that the ) & ( are correct.
Any further suggestions would be most helpful.

Cheers

alsexceladmin

 

[alsexceladmin]

Hi Chiello,
I am still struggling to get the above to work for me. (tried all sorts over two days)
After reproducing your example spreadsheet and ranges, plus the result cells - when I select result cell say - C9 (14) I get - Reference is not valid.
When I faithfully reproduce the formula, despite double-checking my input, I still get - the formula contains an error.

Is it a mistake that I am making inputting? Or when I am transferring your information from here? I feel as though I am duplicated the information exactly, but taking the formula as correct, I must still be doing something wrong somewhere.

The formula for obtaining the result from Range 1 only, works fine. It's just when I try to use the formula for both that I am having trouble.

Any ideas??

alsexceladmin

 

[alsexceladmin]

Hi Chiello,
I am still struggling to get the above to work for me. (tried all sorts over two days)

Still Struggling but..... Could this be the reason? (I have tried everything else)!!!

Unusual Happenings!?!
When entering formula into the cell it displays across the page (even though "Formula" is unticked in the "Tools - Options menu".
When I "Tick" the box in T/O menu it works in reverse by limiting the display to the cell I am using to enter it!
When I enter a totally different formula - anywhere, it works as normal. i.e. Returns a result, or even if error, stays in the one cell display, unless I "Tick" the Formula box in T/O menu, then it displays the formula .

Can anyone help??? It's driving me batty!!

Regards and Thanks

alsexceladmin

 

[howzat]

You may find you are having problems using chiello
if your excel is set up to use commas (,) as forula seperators, where chiello uses semi colons ( ; ).

Try:

=IF(ISNA(INDEX($E$3:$G$5,MATCH(B3,$D$3:$D$5,0),MATCH(A3,$E$2:$G$2,0))),0,INDEX($E$3:$G$5,MATCH(B3,$D$3:$D$5,0),MATCH(A3,$E$2:$G$2,0)))+IF(ISNA(INDEX($E$9:$G$11,MATCH(B3,$D$9:$D$11,0),MATCH(A3,$E$8:$G$8,0))),0,INDEX($E$9:$G$11,MATCH(B3,$D$9:$D$11,0),MATCH(A3,$E$8:$G$8,0)))

BTW, the sumproduct formula as previously posted should work, and does use the same dimentions. It worked for me

 

[alsexceladmin]

cantgetthisdamnthingtowork.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	Spreadsheet			Range1					Range2
2	Ben	Site		Site	IS	IB	NONE		Site	IS	IB	NONE
3	IB	Chee		Brad	18	18	14		Brad	6	6	2
4	IS	Most		Chee	12	18	6		Chee	6	6	2
5	NONE	Harp		Harp	18	18	14		Harp	6	6	2
6	JSA+6	Brad		Most	20	18	14		Mile	6	6	2
Sheet1

Sean,
I tried your formula and - When pasting across it pasted a double cell and the wrong Total (18) After adjustment to match your sheet.
I unmerged the cells and then dragged it down.

If I can just say again just what it is I wish to do…..
With the above sample (normal S/S is much larger) The two Ranges would be situated far right of spreadsheet on the same page.
I need the formula for getting the result(s) of say B3 - A3 matched against both ranges - added together!
That is - e.g. B5 A5 matched in R1gives 20 plus same R2 gives Zero return…Total 20
B4 - A4 gives…18 and 6 Total 24
B6 - A6 gives…14 and 2 Total 16
B7 - A7 gives… 0 and 0 Total 0 because there is no JSA+6 in the ranges shown. Can we get it to show a 0 value rather than #N/A?

I don't mind if I get two separate results and then have to add both range returns later as in two cols….Y3+Z3 to give me the total!

Many thanks for you time Sean, it really is appreciated!

Cheers

Alan

 

[howzat]

Hi Alan,

It seems that when I manually calculate the desired result as you have, I get a different result. This may be because you are referring to a different set of data from that which you have post, or am I just missing something?

The colors below correlate the "lookup values" (for lack of a better term), in ranges: A3:B5 and B10:C12, to the corresponding desired results in the "lookup tables, in ranges: D2:G6 and I2:L6.

The results you desire are shown in D8:F12, and the results I manually pick up are range:G8:I12, which differ from yours, and where some confusion may lie.

The formula results are as follows:
J10:J12 contain the results of lookup up in range 1 only and is as follows:
In J10  =SUMPRODUCT(($D$3:$D$6=B10)*($E$2:$G$2=C10)*($E$3:$G$6))
copied down.

K10:K12 contain the results of lookup up in range 2 only and is as follows:

In K10 =SUMPRODUCT(($I$3:$I$6=B10)*($J$2:$L$2=C10)*($J$3:$L$6))
copied down.

L10:L12 contain the combine formula giving the total of range 1 and range 2.
In L10 =SUMPRODUCT(($D$3:$D$6=B10)*($E$2:$G$2=C10)*($E$3:$G$6)+($I$3:$I$6=B10)*($J$2:$L$2=C10)*($J$3:$L$6))
copied down.

The same combined formula is included in C2:C6, and looks up the cells in range A3: A6.
In A3: =SUMPRODUCT(($D$3:$D$6=B3)*($E$2:$G$2=A3)*($E$3:$G$6)+($I$3:$I$6=B3)*($J$2:$L$2=A3)*($J$3:$L$6))
copied down. This single formula is essencially all you need.

The cells highlighted in red have no corresponding result from the tables.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Spreadsheet			Range1					Range2
2	Ben	Site	Formula	Site	IS	IB	NONE		Site	IS	IB	NONE
3	IB	Chee	24	Brad	18	18	14		Brad	6	6	2
4	IS	Most	20	Chee	12	18	6		Chee	6	6	2
5	NONE	Harp	16	Harp	18	18	14		Harp	6	6	2
6	JSA+6	Brad	0	Most	20	18	14		Mile	6	6	2
7
8				Youget			Iget			Formula
9		I.E.		Range1	Range2	Total	Range1	Range2	Total	Range1	Range2	Total
10	B4-A4	Most	IS	18	6	24	20	0	20	20	0	20
11	B6-A6	Brad	JSA+6	14	2	16	0	0	0	0	0	0
12	B7-A7	0	0	0	0	0	0	0	0	0	0	0
Sheet1

HTH