Sum of one column based on Month value in another?

[MajesticNJ]

Good morning all,

I have a situation where we have a sales spreadsheet and I need to provide sales values for any given month.

In this case, the date is in one column and includes month, day, year. Date format is MM/DD/YYYY. Column is E.

Then there is another column that contains a dollar value. Column is V.

I need to provide the sum of all dollar values in any given month. Being able to do it by a range of months would also be a helpful plus.

Lastly, the sum would output on a different tab. The data collection tab is named ORDERS.

Hope this is enough information to assist. Thanks in advance!

Fred

 

[ziad alsayed]

please try to post part of your daa, this will be easier to answer.

 

[Trevor G]

Welcome to the forum,

Do you mean something like this:

Dates in a column values in another sum based on month

Excel Workbook
	C	D	E	F	G	H
1	Date	Values
2	01/12/2011	34			Jan	101
3	02/13/2011	34			Feb	34
4	03/03/2011	45			Mar	134
5	01/19/2011	67
6	03/31/2011	89
Sheet2

 

[MajesticNJ]

Thank you for your help. I've been sandbagging here in this forum for years and in most cases have found what I needed. This time I am stumped.

What in this is telling the formula to check based on the month? Sorry if that sounds stupid I'm just not seeing what the differentiator is.
Also, how can I reference the ORDERS tab for the values that need to be gathered?

Welcome to the forum,

Do you mean something like this:

Dates in a column values in another sum based on month

 

[MajesticNJ]

Wait...I think I get it. I have to reference only ONE cell for the month variable that contains the month I am looking for. After that it looks for only cells with that month and in that range. Right? Did that even make sense?

 

[MajesticNJ]

Ok now I just need to figure out how to get the cell values from a different TAB in the spreadsheet.

 

[jasonb75]

You need to be careful using that method, it's reliant on data layout to work correctly, condsider the example below, my formula in column H, Trevor G's formula in column I.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 50px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 31px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Date</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Values</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">01/12/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Jan</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">101</TD><TD style="TEXT-ALIGN: right">101</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">02/13/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Feb</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34</TD><TD style="TEXT-ALIGN: right">34</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03/03/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">45</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Mar</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">134</TD><TD style="TEXT-ALIGN: right">134</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">01/19/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">67</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1000</TD><TD style="TEXT-ALIGN: right">101</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03/31/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">89</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">04/10/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1000</TD><TD> </TD><TD> </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>H2</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G2),$D$2:$D$6)</TD></TR><TR><TD>I2</TD><TD>{=SUM(IF(MONTH($C2)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR><TR><TD>H3</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G3),$D$2:$D$6)</TD></TR><TR><TD>I3</TD><TD>{=SUM(IF(MONTH($C3)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR><TR><TD>H4</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G4),$D$2:$D$6)</TD></TR><TR><TD>I4</TD><TD>{=SUM(IF(MONTH($C4)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR><TR><TD>H5</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$7,"Mmm")=G5),$D$2:$D$7)</TD></TR><TR><TD>I5</TD><TD>{=SUM(IF(MONTH($C5)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Notice the differing results for April.

to pull the information from a different sheet, use the format

=SUMPRODUCT(--(TEXT('your sheet name'!$C$2:$C$6,"Mmm")=G2),'your sheet name'!$D$2:$D$6)

Note the inverted commas surrounding the name, these must be used if the sheet name contains spaces.

 

[Dave Patton]

A formula alternative

G2 is Jan 1, 2011 formatted to your preference
Formula =SUMPRODUCT(--($C$2:$C$6-DAY($C$2:$C$6)+1=G2),$D$2:$D$6)


N.B.
I would just use a Pivot Table.

Either of the above will give correct answers by year and month.

 

[MajesticNJ]

I am trying to make this work but it just isn't.
The only thing I can think of is your H5 formula is looking for Jan Feb Mar etc.

The date values are in the ORDERS tab and in column E. The format is mm/dd/yyyy. I need to show the sum of all orders for the month by month.

I wasn't posting a sample of my data because it is literally one column with the date and one column with dollar values. Didn't think it would be needed.

Does this help?

Fred

You need to be careful using that method, it's reliant on data layout to work correctly, condsider the example below, my formula in column H, Trevor G's formula in column I.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 83px"><COL style="WIDTH: 50px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 31px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Date</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Values</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">01/12/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Jan</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">101</TD><TD style="TEXT-ALIGN: right">101</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">02/13/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Feb</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">34</TD><TD style="TEXT-ALIGN: right">34</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03/03/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">45</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Mar</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">134</TD><TD style="TEXT-ALIGN: right">134</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">01/19/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">67</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1000</TD><TD style="TEXT-ALIGN: right">101</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03/31/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">89</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">04/10/11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1000</TD><TD> </TD><TD> </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>H2</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G2),$D$2:$D$6)</TD></TR><TR><TD>I2</TD><TD>{=SUM(IF(MONTH($C2)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR><TR><TD>H3</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G3),$D$2:$D$6)</TD></TR><TR><TD>I3</TD><TD>{=SUM(IF(MONTH($C3)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR><TR><TD>H4</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$6,"Mmm")=G4),$D$2:$D$6)</TD></TR><TR><TD>I4</TD><TD>{=SUM(IF(MONTH($C4)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR><TR><TD>H5</TD><TD>=SUMPRODUCT(--(TEXT($C$2:$C$7,"Mmm")=G5),$D$2:$D$7)</TD></TR><TR><TD>I5</TD><TD>{=SUM(IF(MONTH($C5)=MONTH($C$2:$C$7),($D$2:$D$7),0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Notice the differing results for April.

to pull the information from a different sheet, use the format

=SUMPRODUCT(--(TEXT('your sheet name'!$C$2:$C$6,"Mmm")=G2),'your sheet name'!$D$2:$D$6)

Note the inverted commas surrounding the name, these must be used if the sheet name contains spaces.

 

[Dave Patton]

Excel Workbook
	E	F	G	H	I	J
1	Date	Values		2011	Sum by Month
2	Jan 12, 2011	34		January	101
3	Feb 13, 2011	34		February	34
4	Mar 03, 2011	45		March	134
5	Jan 19, 2011	67		April	250
6	Mar 31, 2011	89
7	Apr 16, 2011	200
8	Apr 04, 2011	50		Sum of Values
9				Years	Date	Total
10				2011	Jan	101
11					Feb	34
12					Mar	134
13					Apr	250
14				Grand Total		519
15
Orders

Excel 2003

Cell Formulas
Range	Formula
I2	=SUMPRODUCT(--($E$2:$E$100-DAY($E$2:$E$100)+1=H2),$F$2:$F$100)

Excel Workbook
Name	Refers To
Product	='1b'!$C$2:$C$10
Workbook Defined Names

You may already have solved your question but you can review this message.
Try using Excel's Evaluate Formula to review the logic and ignore the html named range "product".

Criteria date in C3 on the summary sheet; edit the formula as required.
Criteria C3 is January 1, 2011
Criteria D3 is March 31, 2011 see formula below for calculation of total for quarter.
The data sheet is Orders; please note that example uses Columns E and F.

=SUMPRODUCT(--(Orders!$E$2:$E$100-DAY(Orders!$E$2:$E$100)+1=C3),(Orders!$F$2:$F$100))
=SUMPRODUCT(--(Orders!$E$2:$E$100>=C3),--(Orders!$E$2:$E$100<=D3),(Orders!$F$2:$F$100))