formula needed..

[tygrl510]

Hi,

I have a list of products with similar names, i.e. Lemon, Lemon Meringue, Apple, Apple Pie, etc. with values in one sheet.

Excel Workbook
	B	C	D	E	F
5	PROD	CY10 Q1	CY10 Q2	CY10 Q3	CY10 Q4
6	Lemon	10	5	20	3
7	Lemon Meringue	30	15	45	60
8	Lemon Juice	3	5	6	7
9	Apple	10	5	20	3
10	Apple Pie	30	15	45	60
11	Apple Juice	3	5	6	7
Sheet1

Excel Workbook
	B	C	D	E	F
13	PROD	CY10 Q1	CY10 Q2	CY10 Q3	CY10 Q4
14	Lemon	43	25	71	70
15	Apple	43	25	71	70
Excel 2007 I'd like to create a second sheet that would add the values based on the common name Sheet1

Excel 2007



Is there a formula that can do this?

Thanks in advance for your help.

Rio

 

[njimack]

Enter the following array formula (confirm with ctrl+shift+enter) into C14 of sheet 2, then copy across and down.

=SUM(IF(ISNUMBER(SEARCH($B14,Sheet1!$B$6:$B$11)),IF(Sheet1!$C$5:$F$5=C$13,Sheet1!$C$6:$F$11)))

 

[tygrl510]

Hi Niel,

Thanks for the formula. The value it's giving me is zero. I'm not sure what to change.

Thanks in advance for your assistance.

Rio

 

[njimack]

Did you enter with ctrl+shift+enter?

 

[tygrl510]

i don't know what that means?

Rio

 

[njimack]

When you enter the formula into the cell, don't just press enter. Press Ctrl+Shift+Enter at the same time. If you do this correctly, the formula will appear in the cell, surrounded by curly brackets "{" and "}"

 

[tygrl510]

Hi Niel,

I redid the formula with the crtl+shift+enter..and it came with the "{" "}", but it still returns zeros.

Thanks,
Rio

 

[njimack]

The formula definitely works but I can't post a screenshot as I'm at work. Are your ranges on both sheets identical to the sample you posted in your original post?

 

[tygrl510]

No, they are on separate sheets. One is called "Top Products", the other "Top Products Comparison".

Rio

 

[njimack]

Then you'll need to change all references to Sheet1 to the name of the sheet that holds your data.