Help with Functions in Excel 2013

[sree_kalpax]

Hi,
I have 2 sheets in excel (sheet1 & sheet2), in sheet1 I have a column of data in column A (say A1:A4), and in sheet2 I have a matrix of data from A1:F6, the column A data of sheet1 is a subset of column A data in sheet2, now what I want to do is...check whether any cell in column A of sheet1 exists in column of sheet2 and if so, I would like to fix that row in sheet2 and make a selective addition of that row ie., to illustrate with an example,

sheet1
A B C D E
1 $ $$ $$$ $$$$
2 abc
3 def
4 ghi
5 xyz

sheet2
A B C D E F
1 $ $$ $$$ $$$$ $ Total
2 abc 1 22 3 0 2 28
3 chi 5 3 0 2 2 12
4 def 35 3 0 0 1 39
5 uvw 8 9 10 11 0 38
6 mno 1 2 1 1 0 5

Final Result
So in sheet 1 B2, I need the sum of "B2+F2" from sheet2.

Regards,
Sreeder

 

[GerryZ]

Hello!
I like this question but I don't understand all of the issue It was nice to see a clear spreadsheet sample instead to use 1000s of words Please provide details of data layout with just few rows and colunms with the expected outcome so we can understand better?
I suggest you to post a nice HTML screen-shot with Mr.Excel HTML Maker (not a picture), if you don’t know how to istall and how to use it watch the video linked in my signature
help us to help you
Thank you in advance!

 

[sree_kalpax]

Hello Gerry,
Thank you for the quick response and I have tried to present my question below with the Mr.Excel HTML Maker add-in which you had given before. Please review the same and let me know if you need further details.

Regards,
Sreeder

Screenshot of Sheet1

Excel 2012
	A	B	C	D	E	F
1
2		$	$$	$$$	$$$$
3	abc	3
4	def		3
5	ghi
6	xyz
7
Sheet1

Screenshot of Sheet2

Excel 2012
	A	B	C	D	E	F	G	H
1
2		$	$$	$$$	$$$$	$	Total
3	abc	1	22	3	0	2	28
4	chi	5	3	0	2	2	12
5	def	35	3	0	0	1	39
6	uvw	8	9	10	11	0	38
7	mno	1	2	1	1	0	5
8
Sheet2

Cell Formulas
Range	Formula
G3	=SUM(B3:F3)

Action Required: Need a formula/function for cell B3/C4 of sheet1 so that the summation of different columns ($/$$/$$$/$$$$) are grouped in the sheet1 when there is a match.

 

[GerryZ]

Sree_kalpax
I'm glad you learned how to make a nice html screen shots now is everything cleared but unfortunately I don't know the answer is a bit difficult for me but I m sure someone will help you
Thank you

 

[gaz_chops]

Try

=SUMPRODUCT((Sheet2!$A$2:$A$6=$A2)*(Sheet2!$B$1:$F$1=B$1),Sheet2!$B$2:$F$6)

 

[sree_kalpax]

Thank you so much, works perfectly! Appreciate the prompt help in this forum.

 

[gaz_chops]

You're welcome.

 

[sree_kalpax]

One follow up question. When I try to use the above function for a very large matrix the flow is breaking due to extreme large size. Any suggestion/tip to make this function workable for a very large matrix (say 90X3000 size).

 

[gaz_chops]

What do you mean by breaking, are you getting an error? What range are you using in your formula?

 

[sree_kalpax]

In the formula you provided, when I increased the range especially for the first 2 terms ((Sheet2!$A$2:$A$6=$A2)*(Sheet2!$B$1:$F$1=B$1)) I see the cell value getting evaluated as "#VALUE!".
Basically the data I am actually working with has column A size of 3000 cells in sheet2 and row 2 sizing of about 90 in sheet2.

When I tried evaluating the value for each term in the formula in excel, I get the following message saying
"The formula is too long. Formulas should not be longer than 8192 characters".