how to Add values if cell contains certain text?

[pilotfuzz]

Hi There, I need some help with a formula...

Here's a basic scenario:

...........A.....................B.................C....................D
1| Customers..............Date..........Product...........Quantity (Kg's)
2| ------------------------------------------------------------
3| Kate's Kitchen........1/1/2011.........S110.............100
4| Paul's Pantry..........3/1/2011........GP220.............600
5| Billy's Bakery..........16/1/2011......S110...............250
6| Steve's Shop..........23/1/2011......GH60...............500
7| Gary's Garden..........25/1/2011......GP55...............325
8| Ben's Bistro.............31/1/2011......GP55................411


Now I'd like a formula where i can say for example: if the value from
C3:C8="S110" then it must add the corresponding quantities for "S110" From
D3:D8 and put the answer in a single cell.
Note that "S110" Appears twice! in D3 value is100 and D5 value is 250.
therefor 100+250 = 350.

Id like to end up with the Table below.

...........A...............B
1| Product............"TOTAL" Quantity (Kg's) for the month of JANUARY
2| -------------------------------------
3| S110.............350
4| GP220.............600
5| GH60..............500
6| GH55..............736

Can any1 help me with a formula for this??

I tried: =if((C3:C8), "S110", (D3:D8), 0) but this doesnt work...

Any help would be greatly appreciated!
Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

SUMIF will do that for you. However, Excel also has a built-in feature called a Pivot Table which also does that sort of summary.

The SUMIF formula in B10 is copied down.

The Pivot Table I have shown in columns F:G.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Customers	Date	Product	Quantity (Kg's)		Sum of Quantity (Kg's)
2	Kate's Kitchen	1/01/11	S110	100		Product	Total
3	Paul's Pantry	3/01/11	GP220	600		GH60	500
4	Billy's Bakery	16/01/11	S110	250		GP220	600
5	Steve's Shop	23/01/11	GH60	500		GP55	736
6	Gary's Garden	25/01/11	GP55	325		S110	350
7	Ben's Bistro	31/01/11	GP55	411		Grand Total	2186
8
9
10	S110	350
11	GP220	600
12	GH60	500
13	GP55	736
14
SUMIF or PT

 

[markmzz]

Maybe one of this two formulas (for all months or only for JANUARY):

Excel 2007<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><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><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Customers</TD><TD>Date</TD><TD>Product</TD><TD>Quantity (Kg's)</TD><TD style="TEXT-ALIGN: right"></TD><TD>Product</TD><TD>"TOTAL" Quantity (Kg's) for all months</TD><TD>"TOTAL" Quantity (Kg's) for the month of JANUARY</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Kate's Kitchen</TD><TD style="TEXT-ALIGN: right">1/1/2011</TD><TD>S110</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right"></TD><TD>S110</TD><TD style="TEXT-ALIGN: right">350</TD><TD style="TEXT-ALIGN: right">350</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Paul's Pantry</TD><TD style="TEXT-ALIGN: right">1/3/2011</TD><TD>GP220</TD><TD style="TEXT-ALIGN: right">600</TD><TD style="TEXT-ALIGN: right"></TD><TD>GP220</TD><TD style="TEXT-ALIGN: right">600</TD><TD style="TEXT-ALIGN: right">600</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Billy's Bakery</TD><TD style="TEXT-ALIGN: right">1/16/2011</TD><TD>S110</TD><TD style="TEXT-ALIGN: right">250</TD><TD style="TEXT-ALIGN: right"></TD><TD>GH60</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Steve's Shop</TD><TD style="TEXT-ALIGN: right">1/23/2011</TD><TD>GH60</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">GP55</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1148</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">736</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Gary's Garden</TD><TD style="TEXT-ALIGN: right">1/25/2011</TD><TD>GP55</TD><TD style="TEXT-ALIGN: right">325</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">7</TD><TD style="BACKGROUND-COLOR: #ffff00">Ben's Bistro</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1/31/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">GP55</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">411</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">8</TD><TD style="BACKGROUND-COLOR: #ffff00">Ben's Bistro</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2/1/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">GP55</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">412</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></TBODY></TABLE>Sheet2


<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">Worksheet 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>G2</TH><TD style="TEXT-ALIGN: left">=SUMIF(C$2:C$8,F2,D$2:D$8)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(C$2:C$8=F2),--(TEXT(B$2:B$8,"mmmm")="JANUARY"),D$2:D$8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Note: Peter's suggestion of Pivot Table is a good idea.

Markmzz

 

[pilotfuzz]

Excellent! Thanks guys that's done the trick!

Just had a look at the Pivot Table and its definitively the easiest/quickest method...

Thanks again for the help and quick feedback!