formula needed..

T

tygrl510

Board Regular
Joined
Feb 9, 2009
Messages
54
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
BCDEF
5PRODCY10 Q1CY10 Q2CY10 Q3CY10 Q4
6Lemon105203
7Lemon Meringue30154560
8Lemon Juice3567
9Apple105203
10Apple Pie30154560
11Apple Juice3567
Sheet1
Excel Workbook
BCDEF
13PRODCY10 Q1CY10 Q2CY10 Q3CY10 Q4
14Lemon43257170
15Apple43257170
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
 
Sort by date Sort by votes
Tygrl510,

Try this with the formula of Njimack (array formula - press Ctrl+Shift+Enter and not only Enter):


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>PROD</TD><TD>CY10 Q1</TD><TD>CY10 Q2</TD><TD>CY10 Q3</TD><TD>CY10 Q4</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">Top Products</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Lemon</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Lemon Meringue</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">45</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Lemon Juice</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Apple</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Apple Pie</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">45</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Apple Juice</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>***************</TD><TD>*******</TD><TD>*******</TD><TD>*******</TD><TD>*******</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Top Products


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>PROD</TD><TD>CY10 Q1</TD><TD>CY10 Q2</TD><TD>CY10 Q3</TD><TD>CY10 Q4</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">Top Products Comparison</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Lemon</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">71</TD><TD style="TEXT-ALIGN: right">70</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Apple</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">71</TD><TD style="TEXT-ALIGN: right">70</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Top Products Comparison


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A2,'Top Products'!$A$2:$A$7)),IF(B$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself





</TD></TR></TBODY></TABLE>


Markmzz
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
I got it to work for the first column, but when I drag the formula to the the other columns, it returns a zero value. Any thoughts?

Thanks,
Rio
 
Upvote 0
tygrl510 said:
I got it to work for the first column, but when I drag the formula to the the other columns, it returns a zero value. Any thoughts?

Thanks,
Rio
Click to expand...

Tygrl510,

Compare yours formulas with the formulas below C2, D2, E2, C3, D3, and E3.

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>PROD</TD><TD>CY10 Q1</TD><TD>CY10 Q2</TD><TD>CY10 Q3</TD><TD>CY10 Q4</TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">Top Products Comparison</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Lemon</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">71</TD><TD style="TEXT-ALIGN: right">70</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Apple</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">71</TD><TD style="TEXT-ALIGN: right">70</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Top Products Comparison


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A2,'Top Products'!$A$2:$A$7)),IF(B$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A2,'Top Products'!$A$2:$A$7)),IF(C$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A2,'Top Products'!$A$2:$A$7)),IF(D$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A2,'Top Products'!$A$2:$A$7)),IF(E$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A3,'Top Products'!$A$2:$A$7)),IF(B$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A3,'Top Products'!$A$2:$A$7)),IF(C$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D3</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A3,'Top Products'!$A$2:$A$7)),IF(D$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E3</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(ISNUMBER(SEARCH($A3,'Top Products'!$A$2:$A$7)),IF(E$1='Top Products'!$B$1:$E$1,'Top Products'!$B$2:$E$7)))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


</TD></TR></TBODY></TABLE>


Markmzz
 
Upvote 0
Here is one you don't have to enter as an array.

in cell B14
=SUMPRODUCT(ISNUMBER(SEARCH($A14,$A$6:$A$11))*($B$5:$E$5=B$13)*(B$6:B$11))


(if data is in A5:E11 and your result section is in A13:E15, ofcourse modify to fit in your sheet names etc...)

or with your references:

=SUMPRODUCT(ISNUMBER(SEARCH($A2,'Top Products'!$A$2:$A$7))*('Top Products'!$B$1:$E$1=B$1)*('Top Products'!B$2:B$7))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Excel VBA to filter by unique value, copy paste into new workbook and save
Replies
2
Views
860
rittersportyummy
R
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
465
RoryA
RoryA
Y
  • Solved
Need formula to match
2
Replies
10
Views
651
yukonexcel
Y
J
  • Question Question
Anniversary/Birthday Dashboard
Replies
1
Views
272
6StringJazzer
6StringJazzer
S
  • Question Question
Concatenate and Summarize Strings Across Sheets
Replies
4
Views
424
smn
S

Forum statistics

Threads
1,224,588
Messages
6,179,743
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top