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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,212
Messages
5,509,865
Members
408,757
Latest member
Jamarr123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top