Sumif problem

DanKokoro

New Member
Joined
Dec 14, 2009
Messages
27
Hi all,

I'm a bit of a noob, I tried searching for a solution to my problem in this forum but couldn't find one so i apologise if this has been asked before.

My problem is this. There are two columns of interest A and B.

Column A contains Dates of individual sales. There are multiple sales per day. In Column B is the quantity sold for that individual sale. E.G.

A........... B
01/07/10 8
01/07/10 10
01/07/10 5
02/07/10 12
02/07/10 15
02/07/10 8
03/07/10 6
03/07/10 7
04/07/10 5

What i need to be able to do is on another page, list all the dates with a sum of the sales on that date. e.g.

So basically. sum column B IF column A = "insert date".

Unfortunately i don't know where to start.

Thank you in advance for your help, it is much appreciated.

P.s. if the dates are confusing - that is for the month of July. I'm from New Zealand so we may write our dates the other way around to many of you.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
It looks like a Pivot Table should suit your needs.

Have you used a pivot table before?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
A much easier approach is to create a Pivot Table!!!
Code:
   A        B   C  D           E     
 1 Date     Amt                      
 2 1/7/2010 8                        
 3 1/7/2010 10     Sum of Amt        
 4 1/7/2010 5      Date        Total 
 5 2/7/2010 12     1/7/2010    23    
 6 2/7/2010 15     2/7/2010    35    
 7 2/7/2010 8      3/7/2010    13    
 8 3/7/2010 6      4/7/2010    5     
 9 3/7/2010 7      Grand Total 76    
10 4/7/2010 5                        
Sheet3
[Table-It] version 09 by Erik Van Geit

See www.contextures.com for tutorials on Pivot Tables

lenze
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Hello,

Also, have a look at the SUMIF function.

Here's an example:

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 64px"><COL style="WIDTH: 19px"><COL style="WIDTH: 83px"><COL style="WIDTH: 97px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-WEIGHT: bold">Dates</TD><TD style="FONT-WEIGHT: bold">Sales</TD><TD></TD><TD style="FONT-WEIGHT: bold">Dates</TD><TD style="FONT-WEIGHT: bold">Sales Per Day</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">01/07/2010</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">01/07/2010</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">01/07/2010</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">02/07/2010</TD><TD style="TEXT-ALIGN: right">35</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">01/07/2010</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">03/07/2010</TD><TD style="TEXT-ALIGN: right">13</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">02/07/2010</TD><TD style="TEXT-ALIGN: right">12</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">04/07/2010</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">02/07/2010</TD><TD style="TEXT-ALIGN: right">15</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">02/07/2010</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">03/07/2010</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">03/07/2010</TD><TD style="TEXT-ALIGN: right">7</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">04/07/2010</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=SUMIF(A$2:A$10,D2,B$2:B$10)</TD></TR><TR><TD>E3</TD><TD>=SUMIF(A$2:A$10,D3,B$2:B$10)</TD></TR><TR><TD>E4</TD><TD>=SUMIF(A$2:A$10,D4,B$2:B$10)</TD></TR><TR><TD>E5</TD><TD>=SUMIF(A$2:A$10,D5,B$2:B$10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Matty
 

paipimenta

Board Regular
Joined
Apr 7, 2010
Messages
103
Sheet2:
Column A....Column B
04/07/10....=sumif('Sheet1'!A:A,$A$1,'Sheet1'!B:B)

This is where $A$1 is the cell with the date to sum, Sheet1 column A has the dates, Sheet1 column B has orders
 

DanKokoro

New Member
Joined
Dec 14, 2009
Messages
27
Awesome thanks alot guys,

Pivot table worked a treat. And i'll be storing those sumif formulas for a later date.

Really appreciate the help you guys are great
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,167
Members
418,430
Latest member
Chlwls808

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
Top